/// <summary>
/// Permet de faire une requète de type UPDATE ou INSERT INTO
/// </summary>
/// <param name="rqt"> Requète</param>
/// <param name="dt"> Database à modifier</param>
/// <returns></returns>
public int Update(string rqt, DataTable dt)
{
if (sqlConnect != null)
{
SqlTransaction trans = sqlConnect.BeginTransaction();
SqlCommand sqlCmd = new SqlCommand(rqt, sqlConnect, trans);
SqlDataAdapter sqlDA = new SqlDataAdapter(sqlCmd);
SqlCommandBuilder build = new SqlCommandBuilder(sqlDA);
sqlDA.UpdateCommand = build.GetUpdateCommand();
sqlDA.InsertCommand = build.GetInsertCommand();
sqlDA.DeleteCommand = build.GetDeleteCommand();
sqlDA.MissingSchemaAction = MissingSchemaAction.AddWithKey;
try
{
int res = sqlDA.Update(dt);
trans.Commit();
return res;
}
catch (DBConcurrencyException)
{
trans.Rollback();
}
}
return 0;
}
private void button2_Click(object sender, EventArgs e) // Ìí¼Ó
{
UpdateGVData();
string sql = string.Format("SELECT map, Model FROM npc WHERE RepresentID = {0}", m_RepresentID);
DataTable tbl_trash = Helper.GetDataTable(sql, Conn);
string strMap = tbl_trash.Rows[0]["map"].ToString().Trim();
string strName = tbl_trash.Rows[0]["Model"].ToString().Trim();
sql = string.Format("SELECT MAX(_index) FROM dic_npc_socket_desc");
tbl_trash = Helper.GetDataTable(sql, Conn);
int newIndex = tbl_trash.Rows[0][0] == DBNull.Value ? 1 : Convert.ToInt32(tbl_trash.Rows[0][0]) + 1;
DataTable tbl = this.dataGridViewX1.DataSource as DataTable;
DataRow row = tbl.NewRow();
row["_index"] = newIndex;
row["Map"] = strMap;
row["RepresentID"] = m_RepresentID;
row["Name"] = strName;
row["Socket"] = m_Socket;
row["FileName"] = this.textBox1.Text;
tbl.Rows.Add(row);
SqlCommandBuilder cmdBuilder = new SqlCommandBuilder(m_adp);
m_adp.InsertCommand = cmdBuilder.GetInsertCommand();
int val = m_adp.Update(tbl);
tbl.AcceptChanges();
}
public static void BuildCommandObjects(string conString, string cmdtxt, ref SqlCommand insertCmd, ref SqlCommand updateCmd, ref SqlCommand deleteCmd)
{
if ((conString == null) || (conString.Trim().Length == 0)) throw new ArgumentNullException( "conString" );
if ((cmdtxt == null) || (cmdtxt.Length == 0)) throw new ArgumentNullException( "cmdtxt" );
try
{
using (SqlConnection sqlConnection = new SqlConnection(conString))
{
using (SqlDataAdapter dataAdapter = new SqlDataAdapter(cmdtxt, sqlConnection))
{
using (SqlCommandBuilder cmdBuilder = new SqlCommandBuilder(dataAdapter))
{
insertCmd = cmdBuilder.GetInsertCommand();
updateCmd = cmdBuilder.GetUpdateCommand();
deleteCmd = cmdBuilder.GetDeleteCommand();
}
}
}
}
catch //(Exception ex)
{
throw;// new MyException(string.Format("Building command objects for table {0} failed", tableName), ex);
}
}
/// <summary>
/// 保存数据表所做的更改（带事务）
/// </summary>
/// <param name="conn">SqlTransaction</param>
/// <param name="trans">SqlTransaction</param>
/// <param name="strSQL">需要更新的表的SQL语句</param>
/// <param name="dt">需要更新的DataTable</param>
public static void SaveChangesOnTrans(SDC.SqlConnection conn, SDC.SqlTransaction trans, string strSQL, SD.DataTable dt)
{
//this.Validate();
try
{
using (sqlcmd = new SDC.SqlCommand(strSQL, conn, trans))
{
using (sqladp = new SDC.SqlDataAdapter(sqlcmd))
{
//sqladp.InsertCommand.Transaction = trans;
using (sqlcmdbd = new SDC.SqlCommandBuilder(sqladp))
{
sqlcmdbd.ConflictOption = SD.ConflictOption.CompareRowVersion;
sqladp.InsertCommand = sqlcmdbd.GetInsertCommand();
sqladp.UpdateCommand = sqlcmdbd.GetUpdateCommand();
sqladp.DeleteCommand = sqlcmdbd.GetDeleteCommand();
sqladp.Update(dt);
}
}
}
}
catch (SDC.SqlException ex)
{
throw ex;
}
catch (Exception ex)
{
throw ex;
}
}
/// <summary>
/// Permet de mettre a jour la base de donnees
/// </summary>
/// <param name="request">requete ayant permis d'avoir la table de base</param>
/// <param name="table">la table dans laquelle il y a les modifications</param>
/// <returns>Le nombre de lignes traitees</returns>
private int UpdateCommandBuilder(string request, DataTable table) {
int res = 0;
using(SqlConnection sqlConnection = new SqlConnection(ConnectString)) {
sqlConnection.Open();
SqlTransaction sqlTransaction = sqlConnection.BeginTransaction();
SqlCommand sqlCommand = new SqlCommand(request, sqlConnection, sqlTransaction);
SqlDataAdapter sqlDataAdapter = new SqlDataAdapter(sqlCommand);
SqlCommandBuilder sqlCommandBuilder = new SqlCommandBuilder(sqlDataAdapter);
sqlDataAdapter.UpdateCommand = sqlCommandBuilder.GetUpdateCommand();
sqlDataAdapter.InsertCommand = sqlCommandBuilder.GetInsertCommand();
sqlDataAdapter.DeleteCommand = sqlCommandBuilder.GetDeleteCommand();
sqlDataAdapter.MissingSchemaAction = MissingSchemaAction.AddWithKey;
try {
res = sqlDataAdapter.Update(table);
sqlTransaction.Commit();
} catch (System.Data.SqlClient.SqlException exc) {
sqlTransaction.Rollback();
}
}
return res;
}
protected void btnAddRates_Click(object sender, EventArgs e)
{
try
{
DataSet rateds = new DataSet();
rateadp.Fill(rateds, "Rate");
DataRow dq = rateds.Tables["Rate"].NewRow();
//Rates Table
dq[1] = txtProductID.Text;
dq[2] = double.Parse(txtamount.Text);
dq[3] = ddstate.Text;
dq[4] = txtEffDate.Text;
dq[5] = txtEndDate.Text;
dq[6] = "N";
dq[8] = DateTime.Now.ToString();
dq[9] = Int32.Parse(txtDiscount.Text);
rateds.Tables["Rate"].Rows.Add(dq);
SqlCommandBuilder com1 = new SqlCommandBuilder(rateadp);
rateadp.InsertCommand = com1.GetInsertCommand();
rateadp.Update(rateds, "Rate");
ClientScript.RegisterStartupScript(this.GetType(), "Success", "<script type='text/javascript'>alert('Rate Added Successfully!');window.location='Show_Products.aspx';</script>'");
}
catch (SqlException ex)
{
switch (ex.Number)
{
case 4060: // Invalid Database
ClientScript.RegisterStartupScript(this.GetType(), "Success", "<script type='text/javascript'>alert('Some Error Occured!');window.location='Show_Products.aspx';</script>'");
break;
case 18456: // Login Failed
ClientScript.RegisterStartupScript(this.GetType(), "Success", "<script type='text/javascript'>alert('Some Error Occured!');window.location='Show_Products.aspx';</script>'");
break;
case 547: // ForeignKey Violation
ClientScript.RegisterStartupScript(this.GetType(), "Success", "<script type='text/javascript'>alert('Some Error Occured!');window.location='Show_Products.aspx';</script>'");
break;
case 2627: // Unique Index/ Primary key Violation/ Constriant Violation
ClientScript.RegisterStartupScript(this.GetType(), "Success", "<script type='text/javascript'>alert('Product Id Already Exists!');window.location='Add_Product.aspx';</script>'");
break;
case 2601: // Unique Index/Constriant Violation
ClientScript.RegisterStartupScript(this.GetType(), "Success", "<script type='text/javascript'>alert('Some Error Occured!');window.location='Show_Products.aspx';</script>'");
break;
default:
break;
}
}
}
public void test()
{
SqlConnection cn = new SqlConnection();
DataSet MesInscriptionsDataSet = new DataSet();
SqlDataAdapter da;
SqlCommandBuilder cmdBuilder;
//Set the connection string of the SqlConnection object to connect
//to the SQL Server database in which you created the sample
//table.
cn.ConnectionString = Splash.STR_CON;
cn.Open();
//Initialize the SqlDataAdapter object by specifying a Select command
//that retrieves data from the sample table.
da = new SqlDataAdapter("select * from Inscription", cn);
//Initialize the SqlCommandBuilder object to automatically generate and initialize
//the UpdateCommand, InsertCommand, and DeleteCommand properties of the SqlDataAdapter.
cmdBuilder = new SqlCommandBuilder(da);
//Populate the DataSet by running the Fill method of the SqlDataAdapter.
da.Fill(MesInscriptionsDataSet, "Inscription");
//Display the Update, Insert, and Delete commands that were automatically generated
//by the SqlCommandBuilder object.
tbx_debug.Text = "Update command Generated by the Command Builder : \r\n";
tbx_debug.Text += "================================================= \r\n";
tbx_debug.Text += cmdBuilder.GetUpdateCommand().CommandText;
tbx_debug.Text += " \r\n";
tbx_debug.Text += "Insert command Generated by the Command Builder : \r\n";
tbx_debug.Text += "================================================== \r\n";
tbx_debug.Text += cmdBuilder.GetInsertCommand().CommandText;
tbx_debug.Text += " \r\n";
tbx_debug.Text += "Delete command Generated by the Command Builder : \r\n";
tbx_debug.Text += "================================================== \r\n";
tbx_debug.Text += cmdBuilder.GetDeleteCommand().CommandText;
tbx_debug.Text += " \r\n";
//Write out the value in the CustName field before updating the data using the DataSet.
tbx_debug.Text += "Année before Update : " + MesInscriptionsDataSet.Tables["Inscription"].Rows[0]["année"] + "\r\n";
//Modify the value of the CustName field.
MesInscriptionsDataSet.Tables["Inscription"].Rows[0]["année"] = "2099" + "\r\n";
//Post the data modification to the database.
da.Update(MesInscriptionsDataSet, "Inscription");
tbx_debug.Text += "Année updated successfully \r\n";
//Close the database connection.
cn.Close();
//Pause
Console.ReadLine();
}
protected void Button1_Click(object sender, EventArgs e)
{
try
{
DataSet ds = new DataSet();
adp.Fill(ds, "Pcr");
DataRow dr = ds.Tables["Pcr"].NewRow();
dr[0] = txttitle.Text;
dr[1] = ddservices.SelectedValue;
ds.Tables["Pcr"].Rows.Add(dr);
SqlCommandBuilder com = new SqlCommandBuilder(adp);
adp.InsertCommand = com.GetInsertCommand();
adp.Update(ds, "Pcr");
value = ddservices.SelectedValue;
titlevalue = txttitle.Text;
Cache["V1"] = value;
Cache["V2"] = titlevalue;
Response.Redirect("Show_Products.aspx");
}
catch (SqlException ex)
{
switch (ex.Number)
{
case 4060: // Invalid Database
ClientScript.RegisterStartupScript(this.GetType(), "Success", "<script type='text/javascript'>alert('Some Error Occured!');window.location='Homepage.aspx';</script>'");
break;
case 18456: // Login Failed
ClientScript.RegisterStartupScript(this.GetType(), "Success", "<script type='text/javascript'>alert('Some Error Occured!');window.location='Homepage.aspx';</script>'");
break;
case 547: // ForeignKey Violation
ClientScript.RegisterStartupScript(this.GetType(), "Success", "<script type='text/javascript'>alert('Some Error Occured!');window.location='Homepage.aspx';</script>'");
break;
case 2627: // Unique Index/ Primary key Violation/ Constriant Violation
ClientScript.RegisterStartupScript(this.GetType(), "Success", "<script type='text/javascript'>alert('Title Already Exists!');window.location='NewPCR.aspx';</script>'");
break;
case 2601: // Unique Index/Constriant Violation
//ClientScript.RegisterStartupScript(this.GetType(), "Success", "<script type='text/javascript'>alert('Some Error Occured!');window.location='Homepage.aspx';</script>'");
break;
default:
break;
}
}
}
public object Execute(SqlCommand Command){
/* It will let user close connection when finished */
SqlDataAdapter ResultantAdapter = new SqlDataAdapter(Command);
SqlCommandBuilder cmdBuilder = new SqlCommandBuilder(ResultantAdapter);
ResultantAdapter.UpdateCommand = cmdBuilder.GetUpdateCommand(true);
ResultantAdapter.DeleteCommand = cmdBuilder.GetDeleteCommand(true);
ResultantAdapter.InsertCommand = cmdBuilder.GetInsertCommand(true);
return ResultantAdapter;
}
public void ImportFile()
{
// get db table
SqlDataAdapter adp = new SqlDataAdapter("SELECT * FROM " + m_strTableName, Conn);
adp.MissingSchemaAction = MissingSchemaAction.AddWithKey;
DataSet ds = new DataSet();
adp.Fill(ds);
System.Data.DataTable tbl = ds.Tables[0];
// clear
tbl.BeginInit();
tbl.Rows.Clear();
// 获得另外一张表npc, 读取name 和 map
SqlDataAdapter adp2 = new SqlDataAdapter("SELECT * FROM npc", Conn);
adp2.MissingSchemaAction = MissingSchemaAction.AddWithKey;
DataSet ds2 = new DataSet();
adp2.Fill(ds2);
System.Data.DataTable tbl_npc = ds2.Tables[0];
// import
string fileContent = FileToString(m_strFileFullName);
fileContent = fileContent.Trim();
string[] arrContents = fileContent.Split(new string[] { "NpcPortraitCameraInfo", "[", "]", "{", "}", "=", ",", "\r\n", "\t", " " },
StringSplitOptions.RemoveEmptyEntries);
int arr_index = 0;
for (int i = 0; i < arrContents.Length / 9; i++)
{
DataRow newRow = tbl.NewRow();
newRow.BeginEdit();
newRow["RepresentID"] = arrContents[arr_index++];
newRow["PositionX"] = arrContents[arr_index++];
newRow["PositionY"] = arrContents[arr_index++];
newRow["PositionZ"] = arrContents[arr_index++];
newRow["LookatX"] = arrContents[arr_index++];
newRow["LookatY"] = arrContents[arr_index++];
newRow["LookatZ"] = arrContents[arr_index++];
newRow["Width"] = arrContents[arr_index++];
newRow["Height"] = arrContents[arr_index++];
DataRow r = tbl_npc.Rows.Find(newRow["RepresentID"]);
newRow["Map"] = r["map"].ToString().Trim();
newRow["Name"] = r["Model"].ToString().Trim();
tbl.Rows.Add(newRow);
newRow.EndEdit();
}
// upload
tbl.EndInit();
SqlCommandBuilder cmdBuilder = new SqlCommandBuilder(adp);
adp.InsertCommand = cmdBuilder.GetInsertCommand();
int val = adp.Update(tbl);
tbl.AcceptChanges();
}
/// <summary>
/// 这个静态构造器将读取web.config中定义的全部连接字符串.
/// 链接和适配器将同时指向同一db, 因此只需要创建一次.
/// </summary>
static DBProcess()
{
string constr = ConfigurationManager.ConnectionStrings["MyConn"]
.ConnectionString;
conn = new SqlConnection(constr);
string command = "select * from tb_personInfo";
adapter = new SqlDataAdapter(command, conn);
SqlCommandBuilder builder = new SqlCommandBuilder(adapter);
builder.GetDeleteCommand(true);
builder.GetInsertCommand(true);
builder.GetUpdateCommand(true);
}
public void InitializeDataAdapter()
{
connection.ConnectionString = connectionString ;
SqlCommand selectCommand = new SqlCommand("SELECT * FROM Student", connection);
dataAdapter.SelectCommand = selectCommand;
SqlCommandBuilder builder = new SqlCommandBuilder(dataAdapter);
dataAdapter.DeleteCommand = builder.GetDeleteCommand();
dataAdapter.UpdateCommand = builder.GetUpdateCommand();
dataAdapter.InsertCommand = builder.GetInsertCommand();
}
public void updateAll()
{
if (ds.HasChanges())
{
SqlConnection conexion = new SqlConnection(strConexion);
conexion.Open();
SqlDataAdapter ad = new SqlDataAdapter("select * from usuarios", conexion); // se hace la select para generar automáticamente le comando select, update y delete
SqlCommandBuilder sqlcb = new SqlCommandBuilder(ad);
ad.InsertCommand=sqlcb.GetInsertCommand();
conexion.Close();
}
}
/// <summary>
/// 保存数据表所做的更改（不带事务）
/// </summary>
/// <param name="ConnStr">连接字符串</param>
/// <param name="strSQL">SQL查询语句</param>
/// <param name="dt">DataGridView的DataSource的DataTable</param>
public static void SaveChanges(string ConnStr, string strSQL, SD.DataTable dt)
{
//this.Validate();
try
{
using (sqlconn = new SDC.SqlConnection(ConnStr))
{
sqlconn.Open();
using (sqlcmd = new SDC.SqlCommand(strSQL, sqlconn))
{
using (sqladp = new SDC.SqlDataAdapter(sqlcmd))
{
using (sqlcmdbd = new SDC.SqlCommandBuilder(sqladp))
{
sqlcmdbd.ConflictOption = SD.ConflictOption.CompareAllSearchableValues;
sqladp.InsertCommand = sqlcmdbd.GetInsertCommand();
sqladp.UpdateCommand = sqlcmdbd.GetUpdateCommand();
sqladp.DeleteCommand = sqlcmdbd.GetDeleteCommand();
sqladp.Update(dt);
}
}
}
}
}
catch (SDC.SqlException ex)
{
throw ex;
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (sqlconn != null)
{
if (sqlconn.State != SD.ConnectionState.Closed)
{
sqlconn.Close();
}
sqlconn.Dispose();
}
}
}
public void ExtractTableParameters(string TableName, IDbDataAdapter adapter,
out DatabaseCache InsertCache,
out DatabaseCache DeleteCache,
out DatabaseCache UpdateCache,
out DatabaseCache IsExistCache,
out DataTable dt
)
{
adapter.SelectCommand.CommandText = "select top 1 * from " + TableName;
DataSet ds = new DataSet();
dt = adapter.FillSchema(ds, SchemaType.Source)[0];
dt.TableName = TableName;
SqlCommandBuilder builder = new SqlCommandBuilder(adapter as SqlDataAdapter);
builder.ConflictOption = ConflictOption.OverwriteChanges;
//builder.SetAllValues = false;
SqlCommand InsertCmd = builder.GetInsertCommand(true);
builder.ConflictOption = ConflictOption.OverwriteChanges;
InsertCache = new DatabaseCache(InsertCmd.CommandText, InsertCmd.Parameters);
InsertCache.CurrentTable = dt;
foreach (DataColumn c in dt.Columns)
{
if (c.AutoIncrement)
{
InsertCache.IsHaveAutoIncrement = true;
InsertCache.SQL += ";Select @@IDENTITY;";
break;
}
}
SqlCommand UpdateCmd = builder.GetUpdateCommand(true);
UpdateCache = new DatabaseCache(UpdateCmd.CommandText, UpdateCmd.Parameters);
UpdateCache.CurrentTable = dt;
SqlCommand DeleteCmd = builder.GetDeleteCommand(true);
DeleteCache = new DatabaseCache(DeleteCmd.CommandText, DeleteCmd.Parameters);
DeleteCache.CurrentTable = dt;
IsExistCache = new DatabaseCache(DeleteCmd.CommandText, DeleteCmd.Parameters);
IsExistCache.CurrentTable = dt;
IsExistCache.SQL = IsExistCache.SQL.Replace("DELETE FROM [" + TableName + "]", "Select count(1) from [" + TableName + "] with(nolock) ");
}
public void updateAll()
{
if (ds.HasChanges())
{
SqlConnection conexion = new SqlConnection(strConexion);
conexion.Open();
SqlDataAdapter ad = new SqlDataAdapter("select * from usuarios left join Historiales on usuarios.NssUsuario = Historiales.usuario", conexion); // se hace la select para generar automáticamente le comando select, update y delete
SqlCommandBuilder sqlcb = new SqlCommandBuilder(ad);
ad.InsertCommand = sqlcb.GetInsertCommand();
ad.UpdateCommand = sqlcb.GetUpdateCommand();
ad.DeleteCommand = sqlcb.GetDeleteCommand();
ad.Update(ds.Usuarios);
conexion.Close();
ds.AcceptChanges(); // Elimina las marcas de inserción, actualización o borrado del dataset
}
}
private void AddStudent()
{
string studentID = txtStudentID.Text;
string studentName = txtStudentName.Text;
string studentGender = "";
if (radMale.Checked)
{
studentGender = "男";
}
else
{
studentGender = "女";
}
string dayOfBirth = txtDayOfBirth.Text;
string department = dropDepartment.SelectedValue;
string address = txtAddress.Text;
try
{
SqlConnection connection = SqlHelper.GetConnection();
SqlDataAdapter dataAdapter = new SqlDataAdapter(selectCommandText, connection);
SqlCommandBuilder builder = new SqlCommandBuilder(dataAdapter);
dataAdapter.InsertCommand = builder.GetInsertCommand();
DataSet dataSet = SqlHelper.GetDataSetBySqlCommand(selectCommandText, connection);
DataRow row = dataSet.Tables[0].NewRow();
row["StudentID"] = studentID;
row["Name"] = studentName;
row["Gender"] = studentGender;
row["DayOfBirth"] = dayOfBirth;
row["Address"] = address;
row["Department"] = department;
dataSet.Tables[0].Rows.Add(row);
dataAdapter.Update(dataSet);
lblInfo.Text = "注册成功，<a href='StudentList.aspx'>点击此处</a>查看所有学生记录";
}
catch (Exception exception)
{
lblInfo.Text = exception.Message;
throw new Exception(exception.Message);
}
}
public static void SetLastID(string tblname, string field, int id, SqlConnection Conn)
{
string sql = string.Format("SELECT * FROM sys_id_pool WHERE tablename='{0}' AND fieldname='{1}'", tblname, field);
DataTable tbl = Helper.GetDataTable(sql, Conn);
DataRow row = null;
try
{
SqlDataAdapter adp = GetAdp(sql);
if (tbl.Rows.Count <= 0)
{
row = tbl.NewRow();
row.BeginEdit();
row["tablename"] = tblname;
row["fieldname"] = field;
row["lastid"] = id;
row.EndEdit();
tbl.Rows.Add(row);
SqlCommandBuilder cmdBuilder = new SqlCommandBuilder(adp);
adp.InsertCommand = cmdBuilder.GetInsertCommand();
}
else
{
row = tbl.Rows[0];
row["lastid"] = id;
SqlCommandBuilder cmdBuilder = new SqlCommandBuilder(adp);
adp.UpdateCommand = cmdBuilder.GetUpdateCommand();
}
int val = adp.Update(tbl);
tbl.AcceptChanges();
}
catch (Exception ex)
{
string err = string.Format("在 SetLastID() 时发生错误。\r\n\r\n错误信息为：{0}", ex.Message);
MessageBox.Show(err, "错误", MessageBoxButtons.OK, MessageBoxIcon.Error);
tbl.RejectChanges();
}
}
static public void AddLogEntry(string state, string content = null)
{
return;
try
{
using (ASL.SQL.DBConnection cxconn = DataStore.CloneCxConnection())
using (SqlCommand sqc
= new SqlCommand("select * from [ActivityLog] where Id = 1", cxconn.Connection))
{
SqlDataAdapter da = new SqlDataAdapter(sqc);
SqlCommandBuilder scb = new SqlCommandBuilder(da);
DataTable dt = new DataTable();
da.Fill(dt);
DataRow dr = dt.Rows.Add();
dr["State"] = state;
if (content != null) dr["Content"] = content;
dr["AppVersion"] = DataStore.AssemblyName + "#" + DataStore.AssemblyVersion;
dr["MachineName"] = Environment.MachineName;
dr["UserDomainName"] = Environment.UserDomainName;
dr["UserName"] = Environment.UserName;
if (state == "connect")
{
dr["CurrentDirectory"] = Environment.CurrentDirectory;
dr["CommandLine"] = Environment.CommandLine;
dr["ConfigConnectionString"] = DataStore.CxConnection.Connection.ConnectionString;
if (DataStore.DbConnection != null)
dr["PlantConnectionString"] = DataStore.DbConnection.Connection.ConnectionString;
dr["OSVersion"] = Environment.OSVersion.ToString();
}
scb.GetInsertCommand();
da.Update(dt);
}
}
catch (Exception exc)
{
throw new AppEx(exc, state); ;
}
}
private void btnComandBuilder2_Click(object sender, EventArgs e)
{
String sql;
//CommandBuilder doesn't support multiple table queries
sql = @"Select Orders.OrderDate, Customers.CompanyName from Orders ";
sql+="Join Customers On Orders.CustomerID=Customers.CustomerID";
sql = @"SELECT* FROM Categories";
using (SqlConnection conn = new SqlConnection(@"Data Source=.\SqlExpress;Integrated Security=true;Initial Catalog=Northwind"))
{
using (SqlDataAdapter adapter = new SqlDataAdapter(sql, conn))
{
//This is supported only against single tables.
using (SqlCommandBuilder builder = new SqlCommandBuilder(adapter))
{
builder.QuotePrefix = "[";
builder.QuoteSuffix = "]";
Console.WriteLine(builder.GetUpdateCommand().CommandText);
Console.WriteLine(builder.GetInsertCommand().CommandText);
Console.WriteLine(builder.GetDeleteCommand().CommandText);
}
}
}
}
private void VehicleInfo_Load(object sender, EventArgs e)
{
//取数据~~
vehicleInfo = vehicle_Management_SystemDataSet1.Vehicle;
string queryString = "select * from " + vehicleInfo.TableName;
adapter = new SqlDataAdapter(
queryString, Properties.Settings.Default.Vechicle_Management_SystemConnectionString);
SqlCommandBuilder builer = new SqlCommandBuilder(adapter);
adapter.InsertCommand = builer.GetInsertCommand();
adapter.DeleteCommand = builer.GetDeleteCommand();
adapter.UpdateCommand = builer.GetUpdateCommand();
adapter.Fill(vehicleInfo);
//讲英文列名改为中文~~
vehicleInfo.Columns["vehicle_id"].ColumnName = "车辆编号";
vehicleInfo.Columns["type"].ColumnName = "车辆型号";
vehicleInfo.Columns["capacity"].ColumnName = "车容量";
vehicleInfo.Columns["plate_id"].ColumnName = "车牌号";
vehicleInfo.Columns["buyingCar_id"].ColumnName = "购车批次";
vehicleInfo.Columns["parking_place"].ColumnName = "停车地点";
vehicleInfo.Columns["enable"].ColumnName = "是否停用";
//数据与控件的绑定~~
bindingSource1.DataSource = vehicleInfo;
bindingNavigator1.BindingSource = bindingSource1;
dataGridView1.DataSource = bindingSource1;
//属性显示顺序~~
dataGridView1.Columns["车牌号"].DisplayIndex = 1;
//不允许用户直接在最下面的行添加新行
dataGridView1.AllowUserToAddRows = false;
//不允许用户直接按Delete键删除行
dataGridView1.AllowUserToDeleteRows = false;
//设为只读，不可直接更改~~
dataGridView1.ReadOnly = true;
}
private void btnAdd_Click(object sender, EventArgs e)
{
if (cbEmpID.Text == "")
{
MessageBox.Show("Please select an Employee ID first");
txtEmpID.Focus();
}
else if (txtTimeIn.Text == "")
{
MessageBox.Show("Please enter time in");
txtTimeIn.Focus();
}
else if (txtTimeOut.Text == "")
{
MessageBox.Show("Please enter time out");
txtTimeOut.Focus();
}
else if (txtOvertimeHours.Text == "")
{
MessageBox.Show("Please enter extra time in hours");
txtOvertimeHours.Focus();
}
else
{
try
{
if (txtTimeIn.Text != "" && txtTimeOut.Text != "")
{
int i = 0, j = 0;
bool validval1 = false, validval2 = false;
string str1 = txtTimeIn.Text.ToString();
string str2 = txtTimeOut.Text.ToString();
foreach (char c in str1)
{
i = i + 1;
if (i > 5)
{
MessageBox.Show("Time is not in proper format");
txtTimeIn.Text = "";
txtTimeOut.Text = "";
return;
}
if (i == 3 && c == ':')
{
validval1 = true;
}
}
foreach (char c in str2)
{
j = j + 1;
if (j > 5)
{
MessageBox.Show("Time is not in proper format");
txtTimeIn.Text = "";
txtTimeOut.Text = "";
return;
}
if (j == 3 && c == ':')
{
validval2 = true;
}
}
if (validval1 == false || validval2 == false)
{
MessageBox.Show("Time In or Time Out is not in proper format");
txtTimeIn.Text = "";
txtTimeOut.Text = "";
return;
}
int num1 = Convert.ToInt32(txtTimeIn.Text.ToString().Substring(0, 2));
int num2 = Convert.ToInt32(txtTimeIn.Text.ToString().Substring(3, 2));
int num3 = Convert.ToInt32(txtTimeOut.Text.ToString().Substring(0, 2));
int num4 = Convert.ToInt32(txtTimeOut.Text.ToString().Substring(3, 2));
if ((num1 < 0 || num1 > 24) || (num3 < 0 || num3 > 24))
{
MessageBox.Show("There are only 24 hours in a day.\nTime In or Time Out is not in proper format.\nEnter the format like 13:60");
txtTimeIn.Text = "";
txtTimeOut.Text = "";
return;
}
if ((num2 < 0 || num2 > 60) || (num1 < 0 || num4 > 60))
{
MessageBox.Show("There are only 60 minutes in an hour.\nTime In or Time Out is not in proper format.\nEnter the format like 13:60");
txtTimeIn.Text = "";
txtTimeOut.Text = "";
return;
}
}
}
catch (Exception ex)
{
MessageBox.Show("Time In or Time Out is not in proper format");
txtTimeIn.Text = "";
txtTimeOut.Text = "";
return;
}
if (txtEmpID.Text != "HR001")
{
try
{
dateTimePicker1.Format = DateTimePickerFormat.Custom;
dateTimePicker1.CustomFormat = "MMMM";
string month = dateTimePicker1.Text;
dateTimePicker1.CustomFormat = "";
dateTimePicker1.CustomFormat = "dd";
string date = dateTimePicker1.Text;
dateTimePicker1.CustomFormat = "";
dateTimePicker1.CustomFormat = "yyyy";
string year = dateTimePicker1.Text;
dateTimePicker1.CustomFormat = "";
dateTimePicker1.CustomFormat = "dddd";
string day = dateTimePicker1.Text;
dateTimePicker1.Format = DateTimePickerFormat.Long;
//*************************
string sql = "SELECT * FROM AttendanceDetails WHERE EmpID=@empid And Date=@date And CurrentMonth=@month And CurrentYear=@yr";
SqlCommand SqlCommand1 = con.CreateCommand();
SqlCommand1.CommandText = sql;
int res;
SqlCommand1.Parameters.Add("@empid", SqlDbType.VarChar, 10);
SqlCommand1.Parameters.Add("@date", SqlDbType.VarChar, 30);
SqlCommand1.Parameters.Add("@month", SqlDbType.VarChar, 30);
SqlCommand1.Parameters.Add("@yr", SqlDbType.VarChar, 30);
SqlCommand1.Parameters[0].Value = txtEmpID.Text;
SqlCommand1.Parameters[1].Value = date;
SqlCommand1.Parameters[2].Value = month;
SqlCommand1.Parameters[3].Value = year;
da = new SqlDataAdapter();
da.SelectCommand = SqlCommand1;
myDataSet = new System.Data.DataSet();
res = da.Fill(myDataSet, "AttendanceDetails");
if (res >= 1)
{
SqlCommand objUpdate = con.CreateCommand();
objUpdate.CommandText = "update AttendanceDetails set AttendanceDetails.[EmpID]='" + txtEmpID.Text + "',AttendanceDetails.[FName]='" + txtFName.Text + "',AttendanceDetails.[Department]='" + txtDepartment.Text + "',AttendanceDetails.[Designation]='" + txtDesignation.Text + "',AttendanceDetails.[TimeIn]='" + txtTimeIn.Text + "',AttendanceDetails.[TimeOut]='" + txtTimeOut.Text + "',AttendanceDetails.[OvertimeHours]=" + Convert.ToInt32(txtOvertimeHours.Text) + " WHERE AttendanceDetails.[EmpID]='" + cbEmpID.SelectedItem + "' And AttendanceDetails.[Date]='" + date + "' And AttendanceDetails.[CurrentDay]='" + day + "' And AttendanceDetails.[CurrentMonth]='" + month + "' And AttendanceDetails.[CurrentYear]='" + year + "'";
objUpdate.Connection = con;
objUpdate.Transaction = objTran;
int res2 = objUpdate.ExecuteNonQuery();
if (res2 >= 1)
{
MessageBox.Show("Daily attendance of " + txtFName.Text + " is added.\nDate: " + date + "\nMonth: " + month, "Attendance Added", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
}
//******************************
else
{
SqlDataAdapter adp = new SqlDataAdapter("select * from AttendanceDetails", con);
DataSet ds = new DataSet();
adp.Fill(ds, "AttendanceDetails");
int var = ds.Tables["AttendanceDetails"].Rows.Count;
DataTable table = null;
table = ds.Tables["AttendanceDetails"];
DataRow newRow = null;
newRow = table.NewRow();
newRow["EmpID"] = txtEmpID.Text;
newRow["FName"] = txtFName.Text;
newRow["Department"] = txtDepartment.Text;
newRow["Designation"] = txtDesignation.Text;
newRow["Date"] = date;
newRow["CurrentDay"] = day;
newRow["CurrentMonth"] = month;
newRow["CurrentYear"] = year;
newRow["TimeIn"] = txtTimeIn.Text;
newRow["TimeOut"] = txtTimeOut.Text;
newRow["OvertimeHours"] = Convert.ToInt32(txtOvertimeHours.Text);
newRow["Remarks"] = txtRemarks.Text;
table.Rows.Add(newRow);
SqlCommandBuilder commandBuilder = null;
commandBuilder = new SqlCommandBuilder(adp);
adp.InsertCommand = commandBuilder.GetInsertCommand();
adp.Update(ds, "AttendanceDetails");
MessageBox.Show("Daily attendance of " + txtFName.Text + " is added.\nDate: " + date + "\nMonth: " + month, "Attendance Added", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
}
catch (Exception exc)
{
MessageBox.Show("Provided data not in a correct format");
}
}
else
{
MessageBox.Show("HR001 is Admin record, its attendance request is rejected.", "Daily Attendance Request Fail", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
}
public String Update_SQL_Data(SqlDataAdapter dataAdapter, DataTable dtbTmp)
{
int row;
// Create SQL Command builder
SqlCommandBuilder cb = new SqlCommandBuilder(dataAdapter);
try
{
cb.GetUpdateCommand();
dataAdapter.DeleteCommand = cb.GetDeleteCommand(true);
dataAdapter.UpdateCommand = cb.GetUpdateCommand(true);
//dataAdapter.UpdateCommand.CommandTimeout = 200;
dataAdapter.InsertCommand = cb.GetInsertCommand(true);
row = dataAdapter.Update(dtbTmp);
dtbTmp.AcceptChanges();
}
catch (Exception ex)
{
// Bắt lỗi
return ex.Message;
}
return "";
}
// Sauvegarde tous les changements effectué dans le dataset
public void SaveDataSet(string tableName, DataSet dataSet)
{
if (dataSet.HasChanges() == false)
return;
switch (connType)
{
case ConnectionType.DATABASE_MSSQL:
{
try
{
var conn = new SqlConnection(connString);
var adapter = new SqlDataAdapter("SELECT * from " + tableName, conn);
var builder = new SqlCommandBuilder(adapter);
adapter.DeleteCommand = builder.GetDeleteCommand();
adapter.UpdateCommand = builder.GetUpdateCommand();
adapter.InsertCommand = builder.GetInsertCommand();
lock (dataSet) // lock dataset to prevent changes to it
{
adapter.ContinueUpdateOnError = true;
DataSet changes = dataSet.GetChanges();
adapter.Update(changes, tableName);
PrintDatasetErrors(changes);
dataSet.AcceptChanges();
}
conn.Close();
}
catch (Exception ex)
{
throw new DatabaseException("Can not save table " + tableName, ex);
}
break;
}
case ConnectionType.DATABASE_ODBC:
{
try
{
var conn = new OdbcConnection(connString);
var adapter = new OdbcDataAdapter("SELECT * from " + tableName, conn);
var builder = new OdbcCommandBuilder(adapter);
adapter.DeleteCommand = builder.GetDeleteCommand();
adapter.UpdateCommand = builder.GetUpdateCommand();
adapter.InsertCommand = builder.GetInsertCommand();
DataSet changes;
lock (dataSet) // lock dataset to prevent changes to it
{
adapter.ContinueUpdateOnError = true;
changes = dataSet.GetChanges();
adapter.Update(changes, tableName);
dataSet.AcceptChanges();
}
PrintDatasetErrors(changes);
conn.Close();
}
catch (Exception ex)
{
throw new DatabaseException("Can not save table ", ex);
}
break;
}
case ConnectionType.DATABASE_MYSQL:
{
return;
}
case ConnectionType.DATABASE_OLEDB:
{
try
{
var conn = new OleDbConnection(connString);
var adapter = new OleDbDataAdapter("SELECT * from " + tableName, conn);
var builder = new OleDbCommandBuilder(adapter);
adapter.DeleteCommand = builder.GetDeleteCommand();
adapter.UpdateCommand = builder.GetUpdateCommand();
adapter.InsertCommand = builder.GetInsertCommand();
DataSet changes;
lock (dataSet) // lock dataset to prevent changes to it
{
adapter.ContinueUpdateOnError = true;
changes = dataSet.GetChanges();
adapter.Update(changes, tableName);
dataSet.AcceptChanges();
}
PrintDatasetErrors(changes);
conn.Close();
}
catch (Exception ex)
{
throw new DatabaseException("Can not save table", ex);
}
break;
}
}
}
private static void ExecutarUsandoCommandBuilder(SqlConnection con)
{
SqlDataAdapter da = new SqlDataAdapter(@"SELECT * FROM CLIENTE", con);
SqlCommandBuilder cmdBuilder = new SqlCommandBuilder(da);
SqlCommand insertCommand = cmdBuilder.GetInsertCommand();
insertCommand.Parameters["@p1"].Value = "Via Command Builder";
insertCommand.Parameters["@p2"].Value = DateTime.Now;
insertCommand.ExecuteNonQuery();
}
public void UpdateData(string DbName, string tableName, DataTable dt, DataGrid dg)
{
connectionStringBuilder.InitialCatalog = DbName;
connection.ConnectionString = connectionStringBuilder.ConnectionString;
dt.TableName = tableName;
using (connection)
{
try
{
connection.Open();
SqlCommandBuilder builder = new SqlCommandBuilder(sda);
sda.InsertCommand = builder.GetInsertCommand();
sda.Update(dt);
sda.Fill(dt);
//MessageBox.Show("Success!!");
}
catch
{
if (MessageBox.Show("Connection to server failed! Do you want to create a local copy of DataTable?",
"Connection failed!", MessageBoxButton.YesNo, MessageBoxImage.Question) == MessageBoxResult.Yes)
{
//dt = dg.ItemsSource as DataTable;
SaveFileDialog sf = new SaveFileDialog();
sf.FileName = "myTable.xml";
sf.ShowDialog();
dt.WriteXml(sf.FileName, true);
}
}
}
}
public void Refresh()
{
Filter2Sql();
Data = new DataTable();
SqlCommand columnsSelect = new SqlCommand(this._combinedSql, ExportConfiguration.SqlConnection);
DataAdapter = new SqlDataAdapter(columnsSelect);
SqlCommandBuilder sqlBuilder = new SqlCommandBuilder(DataAdapter);
DataAdapter.UpdateCommand = sqlBuilder.GetUpdateCommand();
DataAdapter.InsertCommand = sqlBuilder.GetInsertCommand();
DataAdapter.DeleteCommand = sqlBuilder.GetDeleteCommand();
DataAdapter.Fill(Data);
OnPropertyChanged("Data");
}
private void add_Click(object sender, EventArgs e)
{
if (firstname_text1.Text == "")
{
MessageBox.Show("Please Enter Name");
return;
}
if (empdet_text1.Text == "")
{
MessageBox.Show("Please Enter Employee ID");
return;
}
if (empswd_text1.Text == "")
{
MessageBox.Show("Please Enter Password");
return;
}
if (dept_text1.Text == "")
{
MessageBox.Show("Please Select the Department");
return;
}
if (address_text1.Text == "")
{
MessageBox.Show("Please Enter Address");
return;
}
if (phone_text1.Text != "")
{
int i = 0;
string str = phone_text1.Text.ToString();
foreach (char c in str)
{
if (Char.IsLetter(c))
{
MessageBox.Show("You cannot enter characters in Phone Number");
phone_text1.Text = "";
return;
}
i = i + 1;
if (i > 10)
{
MessageBox.Show("Phone number is not in proper format");
phone_text1.Text = "";
return;
}
}
}
if (cell_text1.Text != "")
{
int i = 0;
string str = cell_text1.Text.ToString();
foreach (char c in str)
{
if (Char.IsLetter(c))
{
MessageBox.Show("You cannot enter characters in Cell Number");
cell_text1.Text = "";
return;
}
i = i + 1;
if (i > 10)
{
MessageBox.Show("Mobile number is not in proper format");
cell_text1.Text = "";
return;
}
}
}
if (sex_text1.Text == "" | sex_text1.Text == "select one")
{
MessageBox.Show("Please Select Sex");
return;
}
if (education_text1.Text == "")
{
MessageBox.Show("Please Enter Education");
return;
}
if (desig_text1.Text == "")
{
MessageBox.Show("Please Select Designation");
return;
}
if (doj_text1.Text == "")
{
MessageBox.Show("Please Select Date Of Joining in the company");
return;
}
if (Convert.ToDateTime(doj_text1.Text) <= Convert.ToDateTime(dob_text1.Text))
{
MessageBox.Show("Date of Joining must be greater than Date of Birth. Please Check!!!");
return;
}
else
{
if (email_text1.Text != "")
{
bool validval1 = false, validval2 = false;
string str = email_text1.Text.ToString();
foreach (char c in str)
{
if (c == '@')
{
validval1 = true;
break;
}
}
foreach (char c in str)
{
if (c == '.')
{
validval2 = true;
break;
}
}
if (validval1 == false || validval2 == false)
{
MessageBox.Show("Email address is not in proper format.\nPlease enter the address [email protected]");
email_text1.Text = "";
return;
}
}
try
{
con = new System.Data.SqlClient.SqlConnection(GlobalClass.conn);
con.Open();
SqlDataAdapter adp = new SqlDataAdapter("select * from EmployeeDetails", con);
DataSet ds = new DataSet();
adp.Fill(ds, "EmployeeDetails");
int var = ds.Tables["EmployeeDetails"].Rows.Count;
DataTable table = null;
table = ds.Tables["EmployeeDetails"];
DataRow newRow = null;
newRow = table.NewRow();
newRow["EmpID"] = empdet_text1.Text;
newRow["FName"] = firstname_text1.Text;
newRow["MName"] = middlename_text1.Text;
newRow["LName"] = lastname_text1.Text;
newRow["Password"] = empswd_text1.Text;
newRow["Department"] = dept_text1.Text;
newRow["Address"] = address_text1.Text;
newRow["Phone"] = phone_text1.Text;
newRow["Mobile"] = cell_text1.Text;
newRow["Dob"] = dob_text1.Text;
newRow["Sex"] = sex_text1.Text;
newRow["Education"] = education_text1.Text;
newRow["Designation"] = desig_text1.Text;
newRow["Email"] = email_text1.Text;
newRow["Doj"] = doj_text1.Text;
table.Rows.Add(newRow);
SqlCommandBuilder commandBuilder = null;
commandBuilder = new SqlCommandBuilder(adp);
adp.InsertCommand = commandBuilder.GetInsertCommand();
adp.Update(ds, "EmployeeDetails");
con.Close();
MessageBox.Show("New Account of " + firstname_text1.Text + " is Created\nLogin Name: " + empdet_text1.Text + "\nPassword: "******"Account Created",
MessageBoxButtons.OK, MessageBoxIcon.Information);
empdet_text1.Text = "";
firstname_text1.Text = "";
empswd_text1.Text = "";
middlename_text1.Text = "";
lastname_text1.Text = "";
dept_text1.SelectedIndex = -1;
desig_text1.SelectedIndex = -1;
address_text1.Text = "";
phone_text1.Text = "";
email_text1.Text = "";
cell_text1.Text = "";
education_text1.Text = "";
sex_text1.SelectedIndex = -1;
}
catch (Exception exc)
{
MessageBox.Show("Employee ID is not available.\nPlease choose another Employee ID\n");
empdet_text1.Focus();
}
}
}
public void Test_DataAdapter()
{
#region "是否测试 SqlDataAdapter.SelectCommand, SqlDataAdapter.UpdateCommand, SqlDataAdapter.InsertCommand, SqlDataAdapter.DeleteCommand 属性?"
if (this.tracer.WaitInputAChar("是否测试 SqlDataAdapter.SelectCommand, SqlDataAdapter.UpdateCommand, SqlDataAdapter.InsertCommand, SqlDataAdapter.DeleteCommand 属性?") == 'y')
{
try
{
using (DataSet dataSet = new DataSet())
{
this.tracer.WriteLine("说明: 没有 SelectCommand 的 DataAdapter 使用 Fill 方法将导致异常");
SqlDataAdapter adapter = new SqlDataAdapter();
try
{ adapter.Fill(dataSet); }
catch (Exception err)
{ this.tracer.WriteLine(string.Format("异常: {0}", err.Message)); }
}
using (DataSet dataSet = new DataSet())
{
this.tracer.WriteLine("说明: 使用多个 select 语句返回多张表");
SqlConnection connection = new SqlConnection(this.connectionString);
this.tracer.WriteLine("创建拥有两个 select 语句的 SqlCommand");
SqlCommand selectCommand = new SqlCommand("select name, age, classname from student;select name from class", connection);
SqlDataAdapter adapter = new SqlDataAdapter();
adapter.SelectCommand = selectCommand;
adapter.Fill(dataSet);
int index = 1;
foreach (DataTable table in dataSet.Tables)
{
this.tracer.WriteLine(string.Format("表: {0}", table.TableName));
foreach (DataRow row in table.Rows)
{
this.tracer.Write(string.Format("行 [{0}]:", index++));
foreach (DataColumn column in table.Columns)
this.tracer.Write(string.Format("{0} = <{1}>;", column.ColumnName, row[column]));
this.tracer.WriteLine();
}
}
}
using (DataSet dataSet = new DataSet())
{
this.tracer.WriteLine("说明: 更新 DataSet 时, 如果存在 RowState 为 Added, Modified, Deleted 的 DataRow, 则 SqlDataAdapter 需要对应的 InsertCommand, UpdateCommand, DeleteCommand");
SqlConnection connection = new SqlConnection(this.connectionString);
connection.Open();
SqlCommand selectCommand = new SqlCommand("select name, age, classname from student", connection);
SqlDataAdapter adapter = new SqlDataAdapter();
adapter.SelectCommand = selectCommand;
SqlCommandBuilder builder = new SqlCommandBuilder(adapter);
adapter.UpdateCommand = builder.GetUpdateCommand();
adapter.InsertCommand = builder.GetInsertCommand();
adapter.DeleteCommand = builder.GetDeleteCommand();
adapter.Fill(dataSet);
this.tracer.WriteLine("对 student 修改, 删除, 新建");
dataSet.Tables[0].Rows[0]["name"] = "haha";
dataSet.Tables[0].Rows[1].Delete();
dataSet.Tables[0].Rows.Add("我的学生", 10, "二年级一班");
using (SqlTransaction transaction = connection.BeginTransaction())
{
adapter.UpdateCommand.Transaction = transaction;
adapter.InsertCommand.Transaction = transaction;
adapter.DeleteCommand.Transaction = transaction;
this.tracer.WriteLine("更新数据库");
adapter.Update(dataSet);
this.tracer.WriteLine("回滚");
transaction.Rollback();
}
connection.Close();
}
}
catch (Exception err)
{ this.tracer.WriteLine(string.Format("异常: {0}", err.Message)); }
this.tracer.WaitPressEnter();
}
#endregion
}
//this builds commands to update, insert, or delete from the database.
private void cbuilder(string table, DataSet cb_dset)
{
SqlCommandBuilder builder = new SqlCommandBuilder(dataAdapter);
dataAdapter.UpdateCommand = builder.GetUpdateCommand();
//MessageBox.Show(dataAdapter.UpdateCommand.CommandText);
dataAdapter.InsertCommand = builder.GetInsertCommand();
//MessageBox.Show(dataAdapter.InsertCommand.CommandText);
dataAdapter.DeleteCommand = builder.GetDeleteCommand();
//MessageBox.Show(dataAdapter.DeleteCommand.CommandText);
try
{
dataAdapter.RowUpdated += new SqlRowUpdatedEventHandler(dataAdapter_RowUpdated);
int rowsupdated = dataAdapter.Update(cb_dset, table);
dataAdapter.RowUpdated -= new SqlRowUpdatedEventHandler(dataAdapter_RowUpdated);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
public void Update_InsertRow ()
{
conn = new SqlConnection (ConnectionManager.Singleton.ConnectionString);
conn.Open ();
DataTable dt = new DataTable ();
adapter = new SqlDataAdapter ("SELECT * FROM employee", conn);
SqlCommandBuilder builder = new SqlCommandBuilder (adapter);
adapter.InsertCommand = builder.GetInsertCommand ();
adapter.Fill (dt);
DateTime now = DateTime.Now;
DateTime doj = new DateTime (now.Year, now.Month, now.Day, now.Hour,
now.Minute, now.Second);
DateTime dob = new DateTime (now.Year, now.Month, now.Day, now.Hour,
now.Minute, now.Second);
dob.Subtract (new TimeSpan (20 * 365, 0, 0, 0));
try {
DataRow newRow = dt.NewRow ();
newRow ["id"] = 6002;
newRow ["fname"] = "boston";
newRow ["dob"] = dob;
newRow ["doj"] = doj;
newRow ["email"] = "*****@*****.**";
dt.Rows.Add (newRow);
adapter.Update (dt);
SqlCommand cmd = conn.CreateCommand ();
cmd.CommandText = "SELECT id, fname, lname, dob, doj, email FROM employee WHERE id = 6002";
dr = cmd.ExecuteReader ();
Assert.IsTrue (dr.Read (), "#A1");
Assert.AreEqual (6002, dr.GetValue (0), "#A2");
Assert.AreEqual ("boston", dr.GetValue (1), "#A3");
Assert.AreEqual (DBNull.Value, dr.GetValue (2), "#A4");
Assert.AreEqual (dob, dr.GetValue (3), "#A5");
Assert.AreEqual (doj, dr.GetValue (4), "#A6");
Assert.AreEqual ("*****@*****.**", dr.GetValue (5), "#A7");
Assert.IsFalse (dr.Read (), "#A8");
dr.Close ();
} finally {
DBHelper.ExecuteSimpleSP (conn, "sp_clean_employee_table");
}
}
/// <summary>
/// 加载数据（返回DataTable）
/// </summary>
/// <param name="ConnStr">连接字符串</param>
/// <param name="strSQL">SQL语句</param>
/// <param name="strTableName">DataTable表名</param>
/// <param name="isTable">是否对应有物理表，需要有更新、保存等命令</param>
public virtual SD.DataTable GetData(string ConnStr, string strSQL, string strTableName, bool isTable)
{
SD.DataTable dt = new SD.DataTable(strTableName);
try
{
using (sqlconn = new SDC.SqlConnection(ConnStr))
{
//sqlconn.Open();
using (sqlcmd = new SDC.SqlCommand(strSQL, sqlconn))
{
//if (sqlcmd == null)
//{
// using (dt = new SD.DataTable(strTableName))
// {
// return dt;
// }
//}
sqlcmd.CommandTimeout = 7200;
using (sqladp = new SDC.SqlDataAdapter(sqlcmd))
{
if (isTable)
{
using (sqlcmdbd = new SDC.SqlCommandBuilder(sqladp))
{
sqlcmdbd.ConflictOption = SD.ConflictOption.CompareAllSearchableValues;
sqladp.InsertCommand = sqlcmdbd.GetInsertCommand();
sqladp.UpdateCommand = sqlcmdbd.GetUpdateCommand();
sqladp.DeleteCommand = sqlcmdbd.GetDeleteCommand();
sqladp.Fill(dt);
return(dt);
}
}
else
{
sqladp.Fill(dt);
return(dt);
}
}
}
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (sqlconn != null)
{
if (sqlconn.State != SD.ConnectionState.Closed)
{
sqlconn.Close();
}
sqlconn.Dispose();
}
}
}
static void Main(string[] args)
{
string theConnectionString = "Data Source=86BBAJI3MJ0T1JY;Initial Catalog=VideoGameStoreDB;Integrated Security=SSPI;";
SqlConnection theConnection = new SqlConnection(theConnectionString);
theConnection.Open();//打开数据库连接
if (theConnection.State == ConnectionState.Open)
Console.WriteLine("Database Connection is open!\n");
SqlCommand theCommend = new SqlCommand();
theCommend.Connection = theConnection;
theCommend.CommandText = "SELECT * FROM product";
try
{
theCommend.CommandType = CommandType.Text;
SqlDataAdapter theDataAdapter = new SqlDataAdapter(theCommend);
SqlCommandBuilder theCommendBuilder = new SqlCommandBuilder(theDataAdapter);
Console.WriteLine(theCommendBuilder.GetInsertCommand().CommandText + "\n\n");
Console.WriteLine(theCommendBuilder.GetUpdateCommand().CommandText + "\n\n");
Console.WriteLine(theCommendBuilder.GetDeleteCommand().CommandText + "\n\n");
}
catch (SqlException sqlexception)
{
Console.WriteLine(sqlexception);
}
catch (Exception exception)
{
Console.WriteLine(exception.Message);
}
}
