put this class on a .cs file in your project. Add a new item (Service Database) which will create a .mdf file (the database file for MSSQL). Double click on the file and open server explorer (view menu). Open the properties tab, and get the ConnectionString property. Replace the connectString field in my DataHelperMS class with the connection string. Please double the backslashes to avoid a compiler error.
In your event handler, just create a new DataHelperMS class and call its ExecuteDataSet / ExecuteNonQuery Methods. It should work perfectly
public class DataHelperMS
{
public static String connectString = "Data Source=.;AttachDbFilename=\"C:\\Users\\RR\\Documents\\Visual Studio 2008\\Projects\\PhilNits Exam Simulator\\PhilNits Exam Simulator\\exam.mdf\";Integrated Security=True;User Instance=True";
public String ConnectionString
{
get {
return connectString;
}
}
SqlConnection conn;
public DataHelperMS()
{
String conString = ConnectionString;
this.conn = new SqlConnection(conString);
}
public int ExecuteNonQuery(String sql)
{
int result = 0;
try
{
conn.Open();
SqlCommand command = conn.CreateCommand();
command.CommandText = sql;
result = command.ExecuteNonQuery();
}
catch (SqlException)
{
result = 0;
throw;
}
finally
{
conn.Close();
}
return result;
}
public DataSet ExecuteDataSet(String sql)
{
DataSet result = null;
try
{
conn.Open();
SqlCommand command = conn.CreateCommand();
command.CommandText = sql;
result = ConvertDataReaderToDataSet(command.ExecuteReader());
}
catch (SqlException ex)
{
ex.ToString();
result = null;
throw;
}
finally
{
conn.Close();
}
return result;
}
public static DataSet ConvertDataReaderToDataSet(SqlDataReader reader)
{
DataSet dataSet = new DataSet();
do
{
DataTable schemaTable = reader.GetSchemaTable();
DataTable dataTable = new DataTable();
if (schemaTable != null)
{
for (int i = 0; i < schemaTable.Rows.Count; i++)
{
DataRow dataRow = schemaTable.Rows[i];
string columnName = (string)dataRow["ColumnName"];
DataColumn column = new DataColumn(columnName, (Type)dataRow["DataType"]);
try
{
dataTable.Columns.Add(column);
}
catch (DuplicateNameException)
{
int count = 0;
while (dataTable.Columns[columnName] != null)
{
columnName += count;
count++;
}
column.ColumnName = columnName;
dataTable.Columns.Add(column);
}
}
dataSet.Tables.Add(dataTable);
while (reader.Read())
{
DataRow dataRow = dataTable.NewRow();
for (int i = 0; i < reader.FieldCount; i++)
dataRow[i] = reader.GetValue(i);
dataTable.Rows.Add(dataRow);
}
}
else
{
DataColumn column = new DataColumn("RowsAffected");
dataTable.Columns.Add(column);
dataSet.Tables.Add(dataTable);
DataRow dataRow = dataTable.NewRow();
dataRow[0] = reader.RecordsAffected;
dataTable.Rows.Add(dataRow);
}
}
while (reader.NextResult());
foreach (DataTable dt in dataSet.Tables)
{
dt.AcceptChanges();
}
return dataSet;
}
}