90
C# Oracle、Sql Server連接（增、刪、改、查）
1、連接oracle，並可以將數據庫的數據顯示在 gridControl上
private void Btn_XS_Click(object sender, EventArgs e)
{
//寫連接串
//ntegrated Security 身份驗證方式
//當為false時，將在連接中指定用戶ID和密碼。
//當為true時，將使用當前的Windows帳戶憑據進行身份驗證。
//可識別的值為true、false、yes、no以及與true等效的sspi。
string ConnectionString = "Data Source=數據庫名;User Id=用戶名;Password=密碼;Integrated Security=no;";
//創建一個新連接
OracleConnection conn=new OracleConnection(ConnectionString);
//以上兩句也可以寫成 OracleConnection conn=new OracleConnection "Data Source=數據庫名;User Id=用戶名;Password=密碼;Integrated Security=no;");
try
{
conn.Open();
//下麵這句話，即使是select....返回一個int類型的數，也要按下麵這麼利用數據集去做
//不可能用一句話來實現
//注意從函數外麵傳遞參數到sql語句中的寫法
//比如傳遞AdNumber
//"selectyhbh from gspuser where yhbh='" + AdNumber + "'"
OracleCommand cmd = new OracleCommand("select * from FY", conn);
OracleDataAdapter oda = new OracleDataAdapter();
oda.SelectCommand = cmd;
DataSet ds = new DataSet();
oda.Fill(ds);
//如果這想要第一行第一列可以這麼寫ds.Tables[0].Rows[0][0]
gridControl1.DataSource = ds.Tables[0].DefaultView;
conn.Close();
}
catch (Exception ee)
{
//如果有錯誤，輸出錯誤信息
MessageBox.Show(ee.Message);
}
finally
{
//關閉連接
conn.Close();
}
}
//修改 string ConnectionString = "DataSource=ORCL;User Id=system;Password=aaaaaa;Integrated Security=no;"; OracleConnection conn=new OracleConnection(ConnectionString); conn.Open(); string str1 = "SQL修改語句'"; //當不傳遞conn時，會提示連接沒有打開 OracleCommand cmd1 = new OracleCommand(str1,conn); //ExecuteNonQuery()對於Update,Insert,Delete 語句執行成功是返回值為該命令所影響的行數 int result=cmd1.ExecuteNonQuery();2、sql server連接，並實現增、刪、改、查
static string MyConn = "server=127.0.0.1;uid=sa;pwd=密碼;database=Text1;Trusted_Connection=no";
SqlConnection MyConnection = new SqlConnection(MyConn);
//此處的表名Table_1，數據庫名Text1，表中一共有3列Name, Salary,id
//增加
private void button1_Click(object sender, EventArgs e)
{ //輸入框
string MyInsert = "insert into Table_1(Name, Salary,id)values('" + Convert.ToString(textBox1.Text) + "','" + Convert.ToString(textBox2.Text) + "','" + Convert.ToString(textBox3.Text)+ "')";
SqlCommand MyCommand = new SqlCommand(MyInsert, MyConnection);
try
{
MyConnection.Open();
MyCommand.ExecuteNonQuery();
MyConnection.Close();
}
catch (Exception ex)
{
Console.WriteLine("{0} Exception caught.", ex);
}
}
//刪除
private void button2_Click(object sender, EventArgs e)
{
string MyDelete = "Delete from Table_1 where id='" + textBox3.Text + "'";
//string MyDelete = "Delete from Table_1 where id='" + textBox1.Text + "'and Name='" + textBox1.Text + "'and Salary='+textBox3.Text+' ";
SqlCommand MyCommand = new SqlCommand(MyDelete, MyConnection);
try
{
MyConnection.Open();
MyCommand.ExecuteNonQuery();
MyConnection.Close();
}
catch (Exception ex)
{
Console.WriteLine("{0} Exception caught.", ex);
}
}
//更新
private void button3_Click(object sender, EventArgs e)
{
string Name = textBox1.Text;
string Salary = textBox2.Text;
string id = textBox3.Text;
string MyUpdate = "Update Table_1 set Name='" + Name + "',Salary='" + Salary + "' where id='" + textBox3.Text+"'";
SqlCommand MyCommand = new SqlCommand(MyUpdate, MyConnection);
try
{
MyConnection.Open();
MyCommand.ExecuteNonQuery();
MyConnection.Close();
}
catch (Exception ex)
{
Console.WriteLine("{0} Exception caught.", ex);
}
}
//查詢數據
private void button4_Click(object sender, EventArgs e)
{
SqlConnection cn = new SqlConnection("server=(local);database=Text1;Uid=sa;Pwd=aaaaaa");
cn.Open();
SqlDataAdapter dap = new SqlDataAdapter("SELECT Name,Salary,id FROM Table_1", cn);
DataSet ds = new DataSet();//實例化DataSet類
dap.Fill(ds, "Table");//添加SQL語句並執行
dataGridView1.DataSource = ds.Tables[0].DefaultView;//顯示數據
}
最後更新：2017-04-03 12:54:10