Monthly Archives: May 2006 - Page 2

SqlCommandBuilder .. The one I forgot to complete the puzzel

Basically:.NET 2.0 is too cool :) ))

After exploring the smo classes the last part of the afternoon was about creating migrating the data. I hadn’t used the ado api fully but remembered something about the commandbuilder. My conclusion : Worth taking a look at.

I do realise that it isn’t the worlds prettiest sql that gets generated but it gets the job done.  And in the case of my 125 tables that i need to migrate FAST it will save me about 1-2 weeks of writing import procedures etc. Generic datatable updates by writing one sql select command.

   18  public void ImportData(DataTable dt)

   19         {

   20             System.Text.StringBuilder sb = new StringBuilder();

   21             using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString))

   22             {

   23                 SqlCommand cmd = new SqlCommand(string.Format(“Select * from {0}”, dt.TableName),conn);

   24                 SqlDataAdapter da = new SqlDataAdapter(cmd);

   25                 SqlCommandBuilder builder = new SqlCommandBuilder(da);

   26                 //da.UpdateBatchSize = 15;

   27                 da.Update(dt);

   28 

   29             }

   30         }

Woohoo… Microsoft.SqlServer.Management.Smo

The problem I was facing is that I need to keep an accounting database in sync with the “real” application this company uses.

I don’t need all the tables but about 125 need to be imported with data and the whole shabang.

In my mind the best way to do that is to have something iterate the whole database, fill up a dataset and start creating the tables in sql if they don’t exist. Next step is to import all the data from the dataset into the new database.

Obviously this can be done if you just look at the dataset generator. It reads schema’s and creates .net objects from database tables.
But how do i create tables ? And how do i do it without having to write long sql strings ?

The answer is : Microsoft.SqlServer.Management.Smo
A managed library to manipulate sql 2005 :)

You can create any number of items with this library including databases, tables, functions, views, stored procedures ….
Hence woohoo.

To support just the simple types (no objects) I now have a class that does exactly what I want it to do import a database and it took me about 70 lines of code :)

All you need to know to get started can be found on the microsoft website or on david haydens website, that’s where I learned of the existence of this namespace.

http://davidhayden.com/blog/dave/archive/2006/01/27/2775.aspx

http://msdn2.microsoft.com/en-US/library/ms162203.aspx

I won’t be putting up any code with this post because it’s too easy.  If you do run into problems, i can provide you with a snippet :)

Adding insert capabilities to the gridview

I didn’t have time to wrap it in a proper control yet. In this post I’ll just be putting the page implementation.

The gridview is cool and to add an insertrow to it can be done by the footer template. How to do it I explained a while ago : http://geekswithblogs.net/casualjim/articles/51360.aspx

If you want to get the controls from the footerrow you will need to address them with GridView.FooterRow.FindControl(“ControlName”);.

For the empty datatemplate it’s a little bit trickier but not that much. It still is a gridviewrow but it’s wrapped in another control. If you use a button control in your empty datatemplate you also can’t use the submit behaviour from then on everything should be familiar. :)

This is the part in the page :

<%

@ Page Language=”C#” Theme=”" AutoEventWireup=”true” CodeFile=”Test.aspx.cs” Inherits=”Test” %>

DOCTYPE html PUBLIC “-//W3C//DTD XHTML 1.0 Transitional//EN” “http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd”>
   <
html xmlns=”http://www.w3.org/1999/xhtml” xml:lang=”en” lang=”en”>
      <
head id=”Head1″ runat=”server”>
         
<title>ASP.NET Insert data in Gridview title>
      head>
<
body>
   
<form id=”form1″ runat=”server”>
      
<asp:Label ID=”Label1″ runat=”server” Text=”Label”>asp:Label>
      
<asp:GridView ID=”GridView1″ ShowFooter=”true” runat=”server”    
         OnRowCommand
=”GridView1_RowCommand1″ AutoGenerateColumns=”false”>
      
<Columns>
         
<asp:TemplateField>
            
<ItemTemplate>
               
<asp:Button Text=”Edit” CommandName=”Edit” CausesValidation=”false” runat=”server” ID=”btEdit” /> 
               <asp:Button Text=”Delete” CommandName=”Delete” CausesValidation=”false” runat=”server” ID=”btDelete” />
            
ItemTemplate>
            
<EditItemTemplate>
               
<asp:Button Text=”Update” CommandName=”Update” CausesValidation=”true” runat=”server” ID=”btUpdate” /> 
               
<asp:Button Text=”Cancel” CommandName=”Cancel” CausesValidation=”false” runat=”server” ID=”btCancel” />
            
EditItemTemplate>
            
<FooterTemplate>
               
<asp:Button Text=”Insert” CommandName=”Insert” CausesValidation=”true” runat=”server” ID=”btInsert” /> 
               
<asp:Button Text=”Cancel” CommandName=”Cancel” CausesValidation=”false” runat=”server” ID=”btCancel” />
            
FooterTemplate>
         
asp:TemplateField>
      
   <asp:TemplateField >
            
<ItemTemplate>
               
<asp:Label ID=”lblValue” Text=’<%# Eval("Name") %> runat=”server”>asp:Label>
            
ItemTemplate>
            
<EditItemTemplate>
                  
<asp:TextBox ID=”tbUpdate” runat=”server” Text=’<% Bind("Name") %>‘>asp:TextBox>
            
EditItemTemplate>
            
<FooterTemplate>
                  
<asp:TextBox ID=”tbInsert” runat=”server” Text=”" >asp:TextBox>
            
FooterTemplate>
            asp:TemplateField>
         Columns>
         <EmptyDataTemplate>
               <asp:TextBox ID=”tbEmptyInsert” runat=”server”>asp:TextBox><br />
               <asp:Button ID=”btSend” Text=”Insert” runat=”server” CommandName=”EmptyInsert” UseSubmitBehavior=”False” />
            EmptyDataTemplate>
         asp:GridView>
      form>
   body>
html>

And the code behind :

   14 public partial class Test : System.Web.UI.Page

   15 {

   16     protected void Page_Load(object sender, EventArgs e)

   17     {

   18         if (!IsPostBack)

   19         {

   20             //Create dummy data

   21             DataTable dt = new DataTable();

   22             DataColumn dc = new DataColumn(“Name”);

   23             dt.Columns.Add(dc);

   24             DataRow dr = dt.NewRow();

   25             dr["Name"] = “Ivan”;

   26 

   27             //Uncomment the following line to have data in the grid :)

   28             //dt.Rows.Add(dr);

   29 

   30             //Bind the gridview

   31             GridView1.DataSource = dt;

   32             GridView1.DataBind();

   33         }

   34         //Recurses through the controls to show the naming of each individual control that is currently in the gridview

   35         RecurseControls(GridView1.Controls[0].Controls);

   36         Label1.Text += GridView1.Controls[0].Controls[0].GetType().Name +

   37     }

   38 

   39     void RecurseControls(ControlCollection ctls)

   40     {

   41         foreach (Control ctl in ctls)

   42         {

   43             if (!ctl.HasControls())

   44                 Label1.Text += ctl.ClientID + ” “ + ctl.GetType().Name +
;

   45             else

   46                 RecurseControls(ctl.Controls);

   47         }

   48     }

   49 

   50     protected void GridView1_RowCommand1(object sender, GridViewCommandEventArgs e)

   51     {

   52         if (e.CommandName == “EmptyInsert”)

   53         {

   54             //handle insert here

   55             TextBox tbEmptyInsert = GridView1.Controls[0].Controls[0].FindControl(“tbEmptyInsert”) as TextBox;

   56             Label1.Text = string.Format(“You would have inserted the name : {0} from the emptydatatemplate”,tbEmptyInsert.Text);

   57 

   58         }

   59         if (e.CommandName == “Insert”)

   60         {

   61             //handle insert here

   62             TextBox tbInsert = GridView1.FooterRow.FindControl(“tbInsert”) as TextBox;

   63             Label1.Text = string.Format(“You would have inserted the name :  {0} from the footerrow”, tbInsert.Text);

   64         }

   65     }

   66 

   67 }

Page 2 of 212