Simon Hibbard's Web Apps Blog

SQL

Set CheckBox ‘checked’ from SQL DB if an entry exists without any code-behind

Found an interesting way to set the checked value of a checkbox as a databound control. Used in a datalist for example.

The checkbox in the datalist bound to a value in the usual way:

<asp:CheckBox ID=”MainCatCbx” runat=”server” Checked='<%# Eval(“Active”)%>’ CssClass=”smalltext12″ />

Then the query, this was fairly complicated as I wanted to join 2 tables and set the checkbox to ticked if a value was found and not-ticked if it wasn’t, but still display all the records.

The important part (bold) adds a column (active) which is detmind by the result of the case statement. If the DBID column is null the the active column is set to 0(zero) or 1 (one) and cast as a bit so that when the binding takes place it is evaluated as true or false for the checkbox checked value.

SelectCommand=”SELECT tb1.DBID, tb1.Title, tb2.DBID,
CASE WHEN tb2.DBID IS NULL THEN CAST(0 AS bit) ELSE CAST(1 AS bit) END AS Active,
tb1.ParentDBID FROM tb1 LEFT OUTER JOIN tb2 ON Specs_NavTbl.DBID = tb2.CatDBID
WHERE (tb2.DBID = ?) OR (tb2.DBID IS NULL)
AND (tb1.ParentDBID = 0)”

I found this to become usefull rather than writing codebehind to populate datasets from the DB and manually loop through and set the checkbox checked values etc.

 


Using SqlBulkCopy to efficiently copy table from Access to SQL Server

SqlBulkCopy is a quick and efficient way to copy a whole table from a source to SQL Server, below shows how to copy from an Access DB to a table in SQL Server.

More info on SqlBulkCopy can be found in this link.

// Setup destination source and delete the table data if already exists

SqlConnection cnn = new SqlConnection(“Data Source=213.111.211.221; Password=pass;User ID=user;Initial Catalog=DB” providerName=”System.Data.OleDb”);

// delete backup data
SqlCommand cmd = new SqlCommand(“DELETE FROM LPH_ProductTable”, cnn);
cnn.Open();
SqlDataReader rdr = cmd.ExecuteReader();
cnn.Close();
rdr.Close();

// Setup source data connection
String connString = (“Provider=Microsoft.Jet.OLEDB.4.0;” + “Data Source=” + Server.MapPath(“folder1/folder2/accessfile.mdb”));

// Create connection object by using the preceding connection string.
OleDbConnection objConn = new OleDbConnection(connString);

// Set command to get table data
OleDbCommand MDBCommand = new OleDbCommand(“Select * FROM LPH_ProductTable”, objConn);
OleDbDataReader rdr2;

// Open connection with the database.
objConn.Open();

// Read data
rdr2 = MDBCommand.ExecuteReader();

// Initializing an SqlBulkCopy object for destinaion
SqlBulkCopy sbc = new SqlBulkCopy(“Data Source=xxx.xxx.xxx.xxx; Password=pass;User ID=user;Initial Catalog=DB” providerName=”System.Data.OleDb”);

// Copying data to destination
sbc.DestinationTableName = “DestinationTableName”;
sbc.WriteToServer(rdr2);

// Closing connection and the others
sbc.Close();
rdr2.Close();
objConn.Close();


Exporting data from remote SQL Server to local SQL Express

One method I have started to use during development is to run a local SQL Express server along with the local running web server for developing and testing. Firstly I could not manage the local SQL Express using Enterprise Manager as I expected to and had to download SQL Server Management Studio Express (which is actually a good free tool).

I came across a problem which took me a while to sort out and wasn’t very obvious. The problem came up when using Enterprise Manager and wanting to export to my local SQL Express server. Enterprise Manager defaults by refering to the local DB as ‘local’, which I thought would be fine but this did not work. What I found is that you must refer to the local SQL Express DB by using:

<machine name>/SQLEXPRESS

In my case my local machine is called CYBER1 so:

CYBER1/SQLEXPRESS

Exporting to SQL Express

(SQLEXPRESS being the default name created during installation of SQL Express Server)