Rayz.com

Welcome to Rayz.com

Accessing SQL Server Through C#

Must include:

using System.Data.SqlClient;

All SQL access is a result of two things. Making a connection to the database and then doing something with it. In that order.

You connect to the database with aconnection string. In C# you can make the connection string on the fly or get it from somewhere. Either case, you must have one. The connection string tells C# how and where to make the connection.

Example of a connection string:

SqlConnection myConn = new
SqlConnection(“user id=username;” +
“password=password;server=serverurl;” +
“Trusted_Connection=yes;” +
“database=database_name; ” +
“connection timeout=30”);

 

Example of opening the connection:

try
{
myConnection.Open();
}
catch(Exception e)
{
Console.WriteLine(e.ToString());
}
finally
{
// whatever code you want to execute whether or not the open succeeded
}

Here’s some stuff from eForms:
dbConn is the connection string.
sqlConn is the SqlConnection object we will use later.

private string dbConn = Config.DbConn;// get conn str from web.config
private SqlConnection sqlConn;

This one just gets a string from the output of an SP:

private string getLatestReleaseNumFromSQL()
{
sqlConn = new SqlConnection(dbConn);
SqlCommand sqlCmd = new SqlCommand(“eFormsRelease_GetCurrentReleaseNumber”, sqlConn);
sqlCmd.CommandType = CommandType.StoredProcedure;
try
{
sqlConn.Open();
string str = sqlCmd.ExecuteScalar().ToString();
return str;
}
catch (Exception e)
{
return null;
}
finally
{
sqlConn.Close();
sqlConn.Dispose();
sqlCmd.Dispose();
}
}

This one gets some data (using a filler method)

private void getDataFromSQL(string releaseNum)
{
dt.Clear();
sqlConn = new SqlConnection(dbConn);
SqlCommand sqlCmd = new SqlCommand(“eFormsRelease_GetReleaseInfo”, sqlConn);
sqlCmd.CommandType = CommandType.StoredProcedure;
SqlParameter parm1 = sqlCmd.Parameters.Add(“@BeginRelease”, SqlDbType.VarChar);
parm1.Direction = ParameterDirection.Input;
parm1.Value = releaseNum;
SqlParameter parm2 = sqlCmd.Parameters.Add(“@EndRelease”, SqlDbType.VarChar);
parm2.Direction = ParameterDirection.Input;
parm2.Value = releaseNum;

try
{
sqlConn.Close();
sqlConn.Open();
SqlDataReader reader = sqlCmd.ExecuteReader();
int i = 0;
while (reader.Read())
{
i++;
fillDataTableSQL(i,reader);
}
reader.Close();
reader.Dispose();
}
catch (Exception e)
{
throw new Exception(e.Message); ;
}
finally
{
sqlConn.Close();
sqlConn.Dispose();
}
}
private void fillDataTableSQL(int i, SqlDataReader reader)
{
try
{
string ty = reader[“Type”].ToString().Trim();
string re = reader[“Release”].ToString().Trim();
string ti = reader[“Title”].ToString().Trim();
string de = reader[“Description”].ToString().Trim();
string da = reader[“ReleaseDate”].ToString().Trim();
string ri = reader[“RecID”].ToString().Trim();
if (isDate(da))
da = DateTime.Parse(da).ToShortDateString();
// { index, type, release, title, descr, date };
string[] str = new string[] { (i*5).ToString(), ty, re, ti, de, da, ri };
dt.LoadDataRow(str, false);
}
catch (Exception e)
{
string msg = “problem ” + e.Message;
throw new Exception(msg);
}
}

This deletes some stuff:

private bool deleteRowsFromSQL()
{
bool okay = false;
if (deletedRows != null && deletedRows.Count > 0)
{
int rec = 0;
sqlConn = new SqlConnection(dbConn);
SqlCommand sqlCmd = new SqlCommand(“eFormsRelease_DeleteReleaseRecord”, sqlConn);
sqlCmd.CommandType = CommandType.StoredProcedure;
SqlParameter parm1 = sqlCmd.Parameters.Add(“@WriteFlag”, SqlDbType.Int);
parm1.Direction = ParameterDirection.Input;
SqlParameter parm2 = sqlCmd.Parameters.Add(“@RecID”, SqlDbType.Int);
parm2.Direction = ParameterDirection.Input;

try
{
// delete em
for (int i = 0; i < deletedRows.Count; i++) { parm1.Value = 9; parm2.Value = Convert.ToInt32(deletedRows[i]); sqlConn.Close(); sqlConn.Open(); rec = sqlCmd.ExecuteNonQuery(); } } catch (Exception ex) { string m = ex.Message; throw new Exception(m); } finally { sqlConn.Close(); sqlConn.Dispose(); } } deletedRows.Clear(); Session["deleteRowsFromSQL"] = null; return okay; }

Writes data to SQL either updates or inserts based on need:

private bool writeDataToSQL()
{
bool okay = true;
if (deletedRows != null && deletedRows.Count > 0)
okay = deleteRowsFromSQL();// get rid of any deleted rows
try
{
// add/insert lines
for (int i = 0; i < dt.Rows.Count; i++) { if (dt.Rows[i][2].ToString().Length > 0 &&
dt.Rows[i][3].ToString().Length + dt.Rows[i][4].ToString().Length > 0 &&
dt.Rows[i][4].ToString().Length > 0
) // skip empty lines
{
if ((int)dt.Rows[i][6] > -1)
{
// update
SQLupdateRow(
dt.Rows[i][6].ToString(), // RecNum (will update row on RecNum)
dt.Rows[i][1].ToString(), // type
dt.Rows[i][2].ToString(), // rel ver
dt.Rows[i][3].ToString(), // title
dt.Rows[i][4].ToString(), // descr
dt.Rows[i][5].ToString() // rel date
);
}
else
{
// insert
SQLinsertRow(
dt.Rows[i][1].ToString(), // type
dt.Rows[i][2].ToString(), // rel ver
dt.Rows[i][3].ToString(), // title
dt.Rows[i][4].ToString(), // descr
dt.Rows[i][5].ToString() // rel date
);
}
}
}
isDirty = false;
edited.Text = “false”;
}
catch (Exception ex)
{
SendMail.Send(“writeDataToSQL.Exception: ” + ex.Message);
okay = false;
}
return okay;
}

inserts some stuff:

private void SQLinsertRow(string type, string releaseVer,
string title, string description, string releaseDate)
{
/*
@Type int,
@ReleaseInfo varchar(10),
@ReleaseDate DateTime,
@Title varchar(50),
@Descr varchar(MAX),
@ByUser nvarchar(255)
*/
sqlConn = new SqlConnection(dbConn);
SqlCommand sqlCmd = new SqlCommand(“eFormsRelease_InsertReleaseInfo”, sqlConn);
sqlCmd.CommandType = CommandType.StoredProcedure;
try
{
SqlParameter parm1 = sqlCmd.Parameters.Add(“@Type”, SqlDbType.Int);
parm1.Direction = ParameterDirection.Input;
parm1.Value = Convert.ToInt32(type);
SqlParameter parm2 = sqlCmd.Parameters.Add(“@ReleaseInfo”, SqlDbType.VarChar);
parm2.Direction = ParameterDirection.Input;
parm2.Value = releaseVer;
SqlParameter parm3 = sqlCmd.Parameters.Add(“@ReleaseDate”, SqlDbType.DateTime);
parm3.Direction = ParameterDirection.Input;
parm3.Value = DateTime.Parse(releaseDate);
SqlParameter parm4 = sqlCmd.Parameters.Add(“@Title”, SqlDbType.VarChar);
parm4.Direction = ParameterDirection.Input;
parm4.Value = title;
SqlParameter parm5 = sqlCmd.Parameters.Add(“@Descr”, SqlDbType.VarChar);
parm5.Direction = ParameterDirection.Input;
parm5.Value = description;
SqlParameter parm6 = sqlCmd.Parameters.Add(“@ByUser”, SqlDbType.VarChar);
parm6.Direction = ParameterDirection.Input;
parm6.Value = userID;

sqlConn.Close();
sqlConn.Open();
sqlCmd.ExecuteNonQuery();
}
catch (Exception ex)
{
int x = 0;
throw new Exception(“SysWhatsNewEditor.SQLinsertRow.Exception: ” + ex.Message);
}
finally
{
sqlConn.Close();
sqlConn.Dispose();
}
}

Updates some stuff:

private void SQLupdateRow(string recID, string type, string releaseVer,
string title, string description, string releaseDate)
{
/*
@RecNum int,
@TypeInfo int,
@ReleaseInfo varchar(10),
@ReleaseDate DateTime,
@Title varchar(50),
@Descr varchar(MAX),
@ByUser nvarchar(255) = null
*/
sqlConn = new SqlConnection(dbConn);
SqlCommand sqlCmd = new SqlCommand(“eFormsRelease_UpdateReleaseInfo”, sqlConn);
sqlCmd.CommandType = CommandType.StoredProcedure;
try
{
SqlParameter parm0 = sqlCmd.Parameters.Add(“@RecNum”, SqlDbType.Int);
parm0.Direction = ParameterDirection.Input;
parm0.Value = Convert.ToInt32(recID);
SqlParameter parm1 = sqlCmd.Parameters.Add(“@TypeInfo”, SqlDbType.Int);
parm1.Direction = ParameterDirection.Input;
parm1.Value = Convert.ToInt32(type);
SqlParameter parm2 = sqlCmd.Parameters.Add(“@ReleaseInfo”, SqlDbType.VarChar);
parm2.Direction = ParameterDirection.Input;
parm2.Value = releaseVer;
SqlParameter parm3 = sqlCmd.Parameters.Add(“@ReleaseDate”, SqlDbType.DateTime);
parm3.Direction = ParameterDirection.Input;
parm3.Value = DateTime.Parse(releaseDate);
SqlParameter parm4 = sqlCmd.Parameters.Add(“@Title”, SqlDbType.VarChar);
parm4.Direction = ParameterDirection.Input;
parm4.Value = title;
SqlParameter parm5 = sqlCmd.Parameters.Add(“@Descr”, SqlDbType.VarChar);
parm5.Direction = ParameterDirection.Input;
parm5.Value = description;
SqlParameter parm6 = sqlCmd.Parameters.Add(“@ByUser”, SqlDbType.VarChar);
parm6.Direction = ParameterDirection.Input;
parm6.Value = userID;

sqlConn.Close();
sqlConn.Open();
sqlCmd.ExecuteNonQuery();
}
catch (Exception ex)
{
int x = 0;
throw new Exception(“SysWhatsNewEditor.SQLupdateRow.Exception: ” + ex.Message);
}
finally
{
sqlConn.Close();
sqlConn.Dispose();
}
}