I have a series of method calls that all share similar qualities. The basic layo

Question

I have a series of method calls that all share similar qualities. The basic layout is

public void CallDataBase()
{
   Utility sqlHelper = new Utility();

   StringBuilder query = new StringBuilder(200);
   query.Append(@"update " + Environment.NewLine);//or insert, select
   query.Append(@" table " + Environment.NewLine);
   //...
   utility.ConnectAndExecuteNonQuery(query);//or query, scalar
}
I'm not a big fan of the StringBuilder in this case, but it does help readability to have line breaks in the inline SQL.

Stored Procedures are not an option (way too much work to convert, have higher priorities). LINQ and EF are not options. Company is on SQL 2000 and there are no concrete plans to upgrade.

Explanation / Answer

I recently refactored a bunch of our database code to use the Dapper ORM. This resulted in code that looks like this.

using (var connection = new SqlConnection(connectionString))
{
connection.Open();

var p = new DynamicParameters();

p.Add("@ServerIP", "192.168.100.200");
p.Add("@PartName", "Dapper");
p.Add("@Config_Data_Modified", false);
p.Add("@LatestRecord", false);
p.Add("@IsRunning", true);
p.Add("@Stopped", false);
p.Add("@SessionID", dbType: DbType.Int32, direction: ParameterDirection.Output);

connection.Execute(
"DECLARE @TmpTable TABLE (ID INT) " +
"INSERT Session(ServerIP, PartName, Config_Data_Modified, LatestRecord, IsRunning, Stopped) " +
"OUTPUT Inserted.SessionID INTO @TmpTable " +
"VALUES (@ServerIP, @PartName, @Config_Data_Modified, @LatestRecord, @IsRunning, @Stopped)" +
"SELECT @SessionID = ID FROM @TmpTable", p);

var sessionID = p.Get<Int32>("@SessionID");
}
This improved the readability of the code tremendously in my opinion. I don't know what all is going on in your Utility class, but if it is just a simple DAL, then it shouldn't be too hard to replace it with a lightweight ORM such as Dapper.

Related Questions

Navigate

• Browse (All)
• Browse I
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.