c#连接SQL数据库的使用方法.doc

c#连接SQL数据库的使用方法最佳答案 1.提取单条记录/using System.Data;/using System.Data.SqlClient; using (SqlConnection cn = new SqlConnection(%1)/ConfigurationManager.ConnectionStringsdb2ConnectionString.ConnectionString/Data Source=myServerAddress;Initial Catalog=myDataBase;User Id=myUsername;Password=myPassword; SqlCommand cmd = new SqlCommand(Select Count(*) From jobs,cn); cn.Open();%2=cmd.ExecuteScalar(); /Message.InnerHtml 2.单值比较/using System.Data;/using System.Data.SqlClient;using (SqlConnection cn = new SqlConnection(%1)/ConfigurationManager.ConnectionStringsdb2ConnectionString.ConnectionString/Data Source=myServerAddress;Initial Catalog=myDataBase;User Id=myUsername;Password=myPassword; SqlCommand cmd = new SqlCommand(Select Count(*) From jobs,cn); cn.Open();%2=cmd.ExecuteScalar(); /Message.InnerHtmlif(%2=%3)%4 3.显示表格/using System.Data;/using System.Data.SqlClient;DataSet ds=null;using (SqlConnection cn = new SqlConnection(%1)/ConfigurationManager.ConnectionStringsdb2ConnectionString.ConnectionString/Data Source=myServerAddress;Initial Catalog=myDataBase;User Id=myUsername;Password=myPassword; cn.Open();cmd=new SqlDataAdapter(Select * From Author,cn);ds=new DataSet();cmd.Fill(ds,%2); /作者MyDataGrid.DataSource=ds.Tables(%2).DefaultView; /作者MyDataGrid.DataBind(); 4.操作表格/using System.Data;/using System.Data.SqlClient;using (SqlConnection cn = new SqlConnection(%1)/ConfigurationManager.ConnectionStringsdb2ConnectionString.ConnectionString/Data Source=myServerAddress;Initial Catalog=myDataBase;User Id=myUsername;Password=myPassword; cn.Open();cmd=new SqlDataAdapter(Select * From Author,cn);ds=new DataSet();cmd.Fill(ds,%2); /作者MyDataGrid.DataSource=ds.Tables(%2).DefaultView; /作者MyDataGrid.DataBind(); 5.数值范围查询/using System.Data;/using System.Data.SqlClient;using (SqlConnection cn = new SqlConnection(%1)/ConfigurationManager.ConnectionStringsdb2ConnectionString.ConnectionString/Data Source=myServerAddress;Initial Catalog=myDataBase;User Id=myUsername;Password=myPassword; 6.关闭时断开连接/using System.Data;/using System.Data.SqlClient;using (SqlConnection cn = new SqlConnection(%1)/ConfigurationManager.ConnectionStringsdb2ConnectionString.ConnectionString/Data Source=myServerAddress;Initial Catalog=myDataBase;User Id=myUsername;Password=myPassword; 7.执行命令/using System.Data;/using System.Data.SqlClient;using (SqlConnection cn = new SqlConnection(%1)/ConfigurationManager.ConnectionStringsdb2ConnectionString.ConnectionString/Data Source=myServerAddress;Initial Catalog=myDataBase;User Id=myUsername;Password=myPassword; SqlCommand cmd = new SqlCommand(insert userRegister_t values( + TextBox1.Text + , + TextBox2.Text + ),cn); cn.Open(); cmd.ExecuteNonQuery(); 7.Oracle8/8i/9i数据库（thin模式）/using System.Data;Oracle Set Your custom connection strings values ODBC New version Driver=Microsoft ODBC for Oracle;Server=myServerAddress;Uid=myUsername;Pwd=myPassword; Old version Driver=Microsoft ODBC Driver for Oracle;ConnectString=OracleServer.world;Uid=myUsername;Pwd=myPassword; OLE DB, OleDbConnection (.NET) Standard security This connection string uses a provider from Microsoft. Provider=msdaora;Data Source=MyOracleDB;User Id=myUsername;Password=myPassword; Standard Security This connection string uses a provider from Oracle. Provider=OraOLEDB.Oracle;Data Source=MyOracleDB;User Id=myUsername;Password=myPassword; Trusted Connection Provider=OraOLEDB.Oracle;Data Source=MyOracleDB;OSAuthent=1; OracleConnection (.NET) Standard Data Source=MyOracleDB;Integrated Security=yes; This one works only with Oracle 8i release 3 or later Specifying username and password Data Source=MyOracleDB;User Id=myUsername;Password=myPassword;Integrated Security=no; This one works only with Oracle 8i release 3 or later Missing the System.Data.OracleClient namespace? Download .NET Managed Provider for Oracle Great article! Features of Oracle Data Provider for .NET by Rama Mohan G. at C# Corner Omiting tnsnames.ora This is another type of Oracle connection string that doesnt rely on you to have a DSN for the connection. You create a connection string based on the format used in the tnsnames.ora file without the need to actually have one of these files on the client pc. SERVER=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=MyHost)(PORT=MyPort)(CONNECT_DATA=(SERVICE_NAME=MyOracleSID);uid=myUsername;pwd=myPassword; Core Labs OraDirect (.NET) Standard User ID=myUsername;Password=myPassword;Host=ora;Pooling=true;Min Pool Size=0;Max Pool Size=100;Connection Lifetime=0; Read more at Core Lab And at the product page Data Shape MS Data Shape Provider=MSDataShape.1;Persist Security Info=False;Data Provider=MSDAORA;Data Source=orac;User Id=myUsername;Password=myPassword; 16.程序计时 DateTime runTime = Convert.ToDateTime(System.Configuration.ConfigurationSettings.AppSettingsTimerRunAt); /运行时间 TimeSpan runTime = new TimeSpan(runTime.Ticks); /现在时间 TimeSpan timeNow = new TimeSpan(DateTime.Now.Ticks); /时间间隔 TimeSpan ts = runTime.Subtract(timeNow); /如果运行时间与当前时间的差大于0，则任务执行时间为当日 if(ts.TotalMilliseconds 0) return ts.TotalMilliseconds; /如果运行时间与当前时间的差小于0，则任务执行时间为次日 Else