执行查询并生成DataTable:
string sql_str="select Number,Name,Sex,Type,Tel from myFriend";
string ConnectString="server=127.0.0.1;UID=sa;PWD=;DATABASE=myData;connect timeout=120";
SqlConnection myConnection;
myConnection = new SqlConnection( ConnectString);
SqlDataAdapter myCommand = new SqlDataAdapter(sql_str, myConnection);
DataSet ds = new DataSet();
myCommand.Fill(ds, "t1");
DataTable dt=ds.Tables ["t1"];执行删除,更新,插入:
string sql_str="select Number,Name,Sex,Type,Tel from myFriend";
string ConnectString="server=127.0.0.1;UID=sa;PWD=;DATABASE=myData;connect timeout=120";
SqlConnection myConnection;
myConnection = new SqlConnection( ConnectString);
SqlDataAdapter myCommand = new SqlDataAdapter(sql_str, myConnection);
DataSet ds = new DataSet();
myCommand.Fill(ds, "t1");
DataTable dt=ds.Tables ["t1"];执行删除,更新,插入:
using System.Drawing;
using System.Collections;
using System.ComponentModel;
using System.Windows.Forms;
using System.Data;
using System.Data.OleDb ;
using System.Data.SqlClient ;namespace telbook
{
/// <summary>
/// Form1 的摘要说明。
/// </summary>
public class Form1 : System.Windows.Forms.Form
{
private System.Windows.Forms.DataGrid dataGrid1;
private System.Windows.Forms.Button deletebn;
private System.Data.DataSet dataSet1;
private System.Data.SqlClient.SqlCommand sqlCommand1;
private System.Data.SqlClient.SqlConnection sqlConnection1;
private System.Data.SqlClient.SqlDataAdapter goldadapter;
private string source;
private System.Data.DataTable goldtable;
private System.Windows.Forms.GroupBox groupBox1;
private System.Windows.Forms.GroupBox groupBox2;
//add my
private System.Windows.Forms.Button insertbn;
private System.Windows.Forms.Button updatebn;
private System.Windows.Forms.TextBox teltxt;
private System.Windows.Forms.TextBox qqtxt;
private System.Windows.Forms.TextBox nameidtxt;
private System.Windows.Forms.TextBox nametxt;
private System.Windows.Forms.Button querybn;
private System.Windows.Forms.Label label1;
private System.Windows.Forms.Label label2;
private System.Windows.Forms.Label label3;
private System.Windows.Forms.Label label4; string connStr = "Provider=SQLOLEDB.1;"
+"Persist Security Info=False;"
+"User ID=sa;"//
+"Data Source=192.168.0.12;"
+"Initial Catalog = pubs;"
+"Password=''"; OleDbConnection oleConn ; OleDbDataAdapter oleDa ; OleDbCommandBuilder oleCmdBld ; DataSet oleDataSet = new DataSet( ) ; //my /// <summary>
/// 必需的设计器变量。
/// </summary>
private System.ComponentModel.Container components = null; public Form1()
{
//
// Windows 窗体设计器支持所必需的
//
InitializeComponent();
init();
//
// TODO: 在 InitializeComponent 调用后添加任何构造函数代码
//
}
{
string ConnectString="server=127.0.0.1;UID=sa;PWD=;DATABASE=myData;connect timeout=120";
";
SqlConnection myConnection = new SqlConnection(ConnectString);
SqlCommand myCommand = new SqlCommand();
try
{
myConnection.Open();
myCommand.Connection = myConnection;
myCommand.CommandText = sql_str;
myCommand.ExecuteNonQuery();
}
catch(Exception err)
{
throw err;
}
finally
{
myCommand.Dispose();
myConnection.Close();
myConnection.Dispose();
}
}
执行删除,更新,插入操作时,可直接调用该函数,只要写出相应的SQL语句就可以了
比方说:
string sql="delete from myFriend where Number='1'";
Command(sql);
/// 清理所有正在使用的资源。
/// </summary>
protected override void Dispose( bool disposing )
{
if( disposing )
{
if (components != null)
{
components.Dispose();
}
}
base.Dispose( disposing );
} #region Windows Form Designer generated code
private void InitializeComponent()
{
this.dataGrid1 = new System.Windows.Forms.DataGrid();
this.deletebn = new System.Windows.Forms.Button();
this.groupBox1 = new System.Windows.Forms.GroupBox();
this.querybn = new System.Windows.Forms.Button();
this.insertbn = new System.Windows.Forms.Button();
this.groupBox2 = new System.Windows.Forms.GroupBox();
this.label4 = new System.Windows.Forms.Label();
this.label3 = new System.Windows.Forms.Label();
this.label2 = new System.Windows.Forms.Label();
this.label1 = new System.Windows.Forms.Label();
this.updatebn = new System.Windows.Forms.Button();
this.teltxt = new System.Windows.Forms.TextBox();
this.qqtxt = new System.Windows.Forms.TextBox();
this.nameidtxt = new System.Windows.Forms.TextBox();
this.nametxt = new System.Windows.Forms.TextBox();
((System.ComponentModel.ISupportInitialize)(this.dataGrid1)).BeginInit();
this.groupBox1.SuspendLayout();
this.groupBox2.SuspendLayout();
this.SuspendLayout();
//
// dataGrid1
//
this.dataGrid1.DataMember = "";
this.dataGrid1.HeaderForeColor = System.Drawing.SystemColors.ControlText;
this.dataGrid1.Name = "dataGrid1";
this.dataGrid1.Size = new System.Drawing.Size(336, 152);
this.dataGrid1.TabIndex = 0;
//
// deletebn
//
this.deletebn.Location = new System.Drawing.Point(8, 16);
this.deletebn.Name = "deletebn";
this.deletebn.Size = new System.Drawing.Size(56, 24);
this.deletebn.TabIndex = 1;
this.deletebn.Text = "delete";
this.deletebn.Click += new System.EventHandler(this.deletebn_Click);
//
// groupBox1
//
this.groupBox1.Controls.AddRange(new System.Windows.Forms.Control[] {
this.querybn,
this.deletebn});
this.groupBox1.Location = new System.Drawing.Point(0, 160);
this.groupBox1.Name = "groupBox1";
this.groupBox1.Size = new System.Drawing.Size(80, 120);
this.groupBox1.TabIndex = 2;
this.groupBox1.TabStop = false;
//
// querybn
//
this.querybn.Location = new System.Drawing.Point(8, 80);
this.querybn.Name = "querybn";
this.querybn.Size = new System.Drawing.Size(56, 24);
this.querybn.TabIndex = 2;
this.querybn.Text = "query";
this.querybn.Click += new System.EventHandler(this.querybn_Click);
//
// insertbn
//
this.insertbn.Location = new System.Drawing.Point(176, 16);
this.insertbn.Name = "insertbn";
this.insertbn.Size = new System.Drawing.Size(72, 23);
this.insertbn.TabIndex = 3;
this.insertbn.Text = "insert";
this.insertbn.Click += new System.EventHandler(this.button1_Click);
//
// groupBox2
//
this.groupBox2.Controls.AddRange(new System.Windows.Forms.Control[] {
this.label4,
this.label3,
this.label2,
this.label1,
this.updatebn,
this.teltxt,
this.qqtxt,
this.nameidtxt,
this.nametxt,
this.insertbn});
this.groupBox2.Location = new System.Drawing.Point(80, 160);
this.groupBox2.Name = "groupBox2";
this.groupBox2.Size = new System.Drawing.Size(256, 120);
this.groupBox2.TabIndex = 3;
this.groupBox2.TabStop = false;
//
// label4
//
this.label4.Location = new System.Drawing.Point(16, 88);
this.label4.Name = "label4";
this.label4.Size = new System.Drawing.Size(48, 16);
this.label4.TabIndex = 8;
this.label4.Text = "tel";
//
//
this.label3.Location = new System.Drawing.Point(16, 64);
this.label3.Name = "label3";
this.label3.Size = new System.Drawing.Size(48, 16);
this.label3.TabIndex = 7;
this.label3.Text = "qq";
//
// label2
//
this.label2.Location = new System.Drawing.Point(16, 40);
this.label2.Name = "label2";
this.label2.Size = new System.Drawing.Size(48, 16);
this.label2.TabIndex = 6;
this.label2.Text = "nameid";
//
// label1
//
this.label1.Location = new System.Drawing.Point(16, 16);
this.label1.Name = "label1";
this.label1.Size = new System.Drawing.Size(48, 16);
this.label1.TabIndex = 5;
this.label1.Text = "name";
//
// updatebn
//
this.updatebn.Location = new System.Drawing.Point(176, 48);
this.updatebn.Name = "updatebn";
this.updatebn.Size = new System.Drawing.Size(72, 24);
this.updatebn.TabIndex = 4;
this.updatebn.Text = "update";
this.updatebn.Click += new System.EventHandler(this.button2_Click);
//
// teltxt
//
this.teltxt.Location = new System.Drawing.Point(80, 88);
this.teltxt.Name = "teltxt";
this.teltxt.Size = new System.Drawing.Size(72, 21);
this.teltxt.TabIndex = 3;
this.teltxt.Text = "";
//
// qqtxt
//
this.qqtxt.Location = new System.Drawing.Point(80, 64);
this.qqtxt.Name = "qqtxt";
this.qqtxt.Size = new System.Drawing.Size(72, 21);
this.qqtxt.TabIndex = 2;
this.qqtxt.Text = "";
//
// nameidtxt
//
this.nameidtxt.Location = new System.Drawing.Point(80, 40);
this.nameidtxt.Name = "nameidtxt";
this.nameidtxt.Size = new System.Drawing.Size(72, 21);
this.nameidtxt.TabIndex = 1;
this.nameidtxt.Text = "";
//
// nametxt
//
this.nametxt.Location = new System.Drawing.Point(80, 16);
this.nametxt.Name = "nametxt";
this.nametxt.Size = new System.Drawing.Size(72, 21);
this.nametxt.TabIndex = 0;
this.nametxt.Text = "";
//
// Form1
//
this.AutoScaleBaseSize = new System.Drawing.Size(6, 14);
this.ClientSize = new System.Drawing.Size(336, 301);
this.Controls.AddRange(new System.Windows.Forms.Control[] {
this.groupBox2,
this.groupBox1,
this.dataGrid1});
this.Name = "Form1";
this.Text = "通讯录";
this.Load += new System.EventHandler(this.Form1_Load);
((System.ComponentModel.ISupportInitialize)(this.dataGrid1)).EndInit();
this.groupBox1.ResumeLayout(false);
this.groupBox2.ResumeLayout(false);
this.ResumeLayout(false); }
private void init()
{
//nameidtxt.Items.Clear();
try
{
source="server=yl2;uid=sa;pwd='sa';database=Northwind"; this.sqlConnection1 = new System.Data.SqlClient.SqlConnection(source);
this.dataSet1 = new System.Data.DataSet();
this.sqlCommand1 = new System.Data.SqlClient.SqlCommand("SELECT * FORM Table1",sqlConnection1);
this.dataSet1.DataSetName = "NewDataSet";
this.dataSet1.Locale = new System.Globalization.CultureInfo("zh-CN"); this.goldadapter = new System.Data.SqlClient.SqlDataAdapter() ;
this.goldadapter.SelectCommand= new System.Data.SqlClient.SqlCommand("SELECT * from Table1",this.sqlConnection1);
this.sqlConnection1.Open();
this.goldadapter.Fill(this.dataSet1,"table1");
this.dataGrid1.DataSource = this.dataSet1.Tables ["Table1"];
//save the data to goldtable
goldtable=this.dataSet1.Tables["table1"]; this.sqlConnection1.Close();
//add the event while the grid change
this.dataGrid1.CursorChanged += new EventHandler(gridcursorchanged);
//set the grid not to edit
//this.dataGrid1.ReadOnly = true; oleConn = new OleDbConnection(connStr) ; oleDa = new OleDbDataAdapter("select * from authors",oleConn) ; oleCmdBld = new OleDbCommandBuilder(oleDa) ; //this.oleConn.Open() ;
this.oleDa.Fill(this.oleDataSet) ;
//this.oleConn.Close() ;
//this.dataGrid1.DataSource = this.oleDataSet.Tables[0] ;
}
catch(Exception inite)
{
MessageBox.Show(this,inite.ToString());
} }
#endregion
/// 应用程序的主入口点。
/// </summary>
[STAThread]
static void Main()
{
Application.Run(new Form1());
}
private void gridcursorchanged(object sender, EventArgs e)//when the grid cursor change
{
try
{
System.Windows.Forms.TextBox [] txtarray;
txtarray = new TextBox[4];
txtarray[0] = nametxt;
txtarray[1] = nameidtxt;
txtarray[2] = qqtxt;
txtarray[3] = teltxt; //get current the grid index
int currow=this.dataGrid1.CurrentRowIndex;
//int maxrow=this.dataGrid1.m
//set text of these textbox
if(currow>=0)
{
for(int i=0;i<4;i++)
{
txtarray[i].Text=(string)this.dataGrid1[currow,i];
}
}
}
catch(Exception evente)
{
MessageBox.Show(this,evente.ToString());
}
} private void Form1_Load(object sender, System.EventArgs e)
{ } private void deletebn_Click(object sender, System.EventArgs e)
{
//first the method
/*try
{
string sqlstr="SELECT * FROM table1";
System.Data.SqlClient.SqlConnection conn = new System.Data.SqlClient.SqlConnection(source) ;
conn.Open();
System.Data.SqlClient.SqlDataAdapter myAdapter = new System.Data.SqlClient.SqlDataAdapter() ; //SqlTransaction myTrans = conn.BeginTransaction(); myAdapter.SelectCommand=new System.Data.SqlClient.SqlCommand(sqlstr,conn);
System.Data.SqlClient.SqlCommandBuilder cmdbuilder= new System.Data.SqlClient.SqlCommandBuilder(myAdapter);
//cmdbuilder.GetDeleteCommand();
DataTable datatable;
DataRow dr;
myAdapter.Fill(dataSet1,"table1");
datatable=this.dataSet1.Tables["table1"];
dr=datatable.Rows[this.dataGrid1.CurrentRowIndex];
MessageBox.Show(this,"select index="+this.dataGrid1.CurrentRowIndex);
dr.Delete(); myAdapter.Update(dataSet1,"table1");
this.dataGrid1.DataSource = this.dataSet1.Tables ["table1"];
//testsql
conn.Close();
}
catch(Exception dele)
{
MessageBox.Show(dele.ToString(),"error");
}*/
///the second method
try
{
this.sqlConnection1.Open();
this.goldadapter.Fill(this.dataSet1,"table1");
DataTable datatable;
datatable=this.dataSet1.Tables["table1"];
int row=this.dataGrid1.CurrentRowIndex;
int i=0;
string delkey=null;
foreach(DataRow r in datatable.Rows)
{
if(i==row)
{
delkey=(string)r.ItemArray[1];
break;
}
i++;
}
string delstr="DELETE FROM table1 WHERE nameid = "+"'"+delkey+"'";
System.Data.SqlClient.SqlCommand delcmd = new System.Data.SqlClient.SqlCommand(delstr,this.sqlConnection1);
delcmd.ExecuteNonQuery(); //refresh these records
this.dataSet1.Clear();
this.goldadapter.Fill(this.dataSet1,"table1");
datatable=this.dataSet1.Tables["table1"];
this.dataGrid1.DataSource=datatable;
//save the data to goldtable
//goldtable=datatable.Copy();
goldtable=this.dataSet1.Tables["table1"]; this.sqlConnection1.Close();
MessageBox.Show(this,"delete the record success");
}
catch(Exception dele)
{
MessageBox.Show(this,dele.ToString());
}
finally
{
if(this.sqlConnection1.State==ConnectionState.Open)
this.sqlConnection1.Close();
} }
[email protected]感激不尽……