January 29, 2007

C# SQL

SQL Dos and Donts
SQLDataAdapter without using SQLCommandBuilder. - Useful SQL sample
Data Access Strategies Using ADO.NET and SQL
Lesson 03: The SqlCommand Object - Simplest example possible to start using SqlCOnnection and SqlCommand

Here is some code to save to an SQL database:
using (SqlConnection connection = new SqlConnection(MYDATABASECONFIGURATION.ConnectionString))
{
    connection.Open();
    SqlTransaction sqlTransaction = connection.BeginTransaction();
    try
    {

        int outcome = SqlStoreSomething(connection, sqlTransaction, thingToStore);
        if (outcome == 1)
        {
            SqlStoreSomethingElse(connection, sqlTransaction, otherThingy);
        }
        else
        {
            throw new ApplicationException(msg);
        }
    }
    catch (Exception ex)
    {
        sqlTransaction.Rollback();
        throw ex;
    }
    sqlTransaction.Commit();
    sqlTransaction.Dispose();
    connection.Close();
}

private int SqlStoreThingey(
        SqlConnection connection,
        SqlTransaction sqlTransaction,
        SomeThingey thingey)
{
    int returnValue = 0;
    if ((thingey.Id == Guid.Empty))
    {
        throw new System.ArgumentOutOfRangeException("Id: " + thingey.Id.ToString());
    }
    if ((thingey.SomeRef == null))
    {
        throw new System.ArgumentNullException("SomeRef");
    }

    using (SqlCommand sqlCmd = new SqlCommand())
    {
        sqlCmd.Connection = connection;
        sqlCmd.Transaction = sqlTransaction;
        sqlCmd.CommandText =
            @"INSERT INTO [dbo].[MasterThingeyTable] ([Id], [SomeRef], [Date] " +
            @") VALUES (@Id, @SomeRef, @Date, )";
        sqlCmd.CommandType = System.Data.CommandType.Text;
        sqlCmd.Parameters.Add(new SqlParameter("@Id", System.Data.SqlDbType.UniqueIdentifier, 0, 

System.Data.ParameterDirection.Input, 0, 0, "Id", System.Data.DataRowVersion.Current, false, null, "", "", ""));
        sqlCmd.Parameters.Add(new SqlParameter("@SomeRef", System.Data.SqlDbType.NVarChar, 0, 

System.Data.ParameterDirection.Input, 0, 0, "SomeRef", System.Data.DataRowVersion.Current, false, null, "", "", ""));
        sqlCmd.Parameters.Add(new SqlParameter("@Date", System.Data.SqlDbType.DateTime, 0, 

System.Data.ParameterDirection.Input, 0, 0, "Date", System.Data.DataRowVersion.Current, false, null, "", "", ""));

        sqlCmd.Parameters[0].Value = ((System.Guid)(thingey.Id));
        sqlCmd.Parameters[1].Value = ((string)(thingey.SomeRef));
        sqlCmd.Parameters[2].Value = ((System.DateTime)(thingey.Date));

        returnValue = sqlCmd.ExecuteNonQuery();
    }
    return returnValue;
}

No comments: