c#连接SQLServer数据库1.建⽴⼀个控制台测试程序简单实现
using (SqlConnection conn = new SqlConnection("Server=.;database=DB_Test;uid=sa;pwd=111111"))
{
//查询
conn.Open();
SqlCommand cmd = new SqlCommand("select * from test", conn);
SqlDataAdapter da = new SqlDataAdapter(cmd);
DataSet ds = new DataSet();
da.Fill(ds);
Console.WriteLine("查询出条⽬数：" + ds.Tables[0].Rows.Count.ToString());
}
using (SqlConnection conn = new SqlConnection("Server=.;database=DB_Test;uid=sa;pwd=111111"))
{
//删除
conn.Open();
SqlCommand cmd1 = new SqlCommand("delete from test where id=14", conn);
int rows = cmd1.ExecuteNonQuery();
Console.WriteLine("删除条⽬数：" + rows);
}
Console.Read();
2.连接字符串配置在config⽂件中
<?xml version="1.0" encoding="utf-8" ?>
<configuration>
<appSettings>
<add key="ConnASK" value="Server=.;database=DB_Test;uid=sa;pwd=111111"/>
</appSettings>
</configuration>
读取
//数据库连接字符串(.config来配置).
public static string connectionString = System.Configuration.ConfigurationManager.AppSettings["ConnASK"];
static void Main(string[] args)
{
try
{
using (SqlConnection conn = new SqlConnection(connectionString))
{
//查询
conn.Open();
SqlCommand cmd = new SqlCommand("select * from test", conn);
SqlDataAdapter da = new SqlDataAdapter(cmd);
DataSet ds = new DataSet();
da.Fill(ds);
Console.WriteLine("查询出条⽬数：" + ds.Tables[0].Rows.Count.ToString());
}
Console.Read();
}
catch (Exception)
{
throw;
}
}
3.带参数的sql 语句执⾏(动软⽣成的)
///<summary>
///增加⼀条数据
///</summary>
public bool st model)
{
StringBuilder strSql=new StringBuilder();
strSql.Append("insert into test(");
strSql.Append(" testName,testTyp,testTime)");
strSql.Append(" values (");
strSql.Append(" @testName,@testTyp,@testTime)");
SqlParameter[] parameters = {
new SqlParameter("@testId", SqlDbType.Int,4),
new SqlParameter("@testName", SqlDbType.VarChar,50),
new SqlParameter("@testTyp", SqlDbType.VarChar,50),
new SqlParameter("@testTime", SqlDbType.Float,4)};
parameters[0].Value = stId;
parameters[1].Value = stName;
parameters[2].Value = stTyp;
parameters[3].Value = stTime;
int rows=ExecuteSql(strSql.ToString(),parameters);
if (rows > 0)
{
return true;
}
else
{
return false;
}
}
///<summary>
///执⾏SQL语句，返回影响的记录数
///</summary>
///<param name="SQLString">SQL语句</param>
///<returns>影响的记录数</returns>
public static int ExecuteSql(string SQLString, params SqlParameter[] cmdParms)
{
using (SqlConnection connection = new SqlConnection(connectionString))
{
using (SqlCommand cmd = new SqlCommand())
{
try
{
PrepareCommand(cmd, connection, null, SQLString, cmdParms);
int rows = cmd.ExecuteNonQuery();
cmd.Parameters.Clear();
return rows;
}
catch (System.Data.SqlClient.SqlException e)
{
throw e;
}
}
}
}
private static void PrepareCommand(SqlCommand cmd, SqlConnection conn, SqlTransaction trans, string cmdText, SqlParameter[] cmdParms)        {
if (conn.State != ConnectionState.Open)
conn.Open();
cmd.Connection = conn;
cmd.CommandText = cmdText;
if (trans != null)
sqltransaction什么意思cmd.Transaction = trans;
cmd.CommandType = CommandType.Text;//cmdType;
if (cmdParms != null)
{
foreach (SqlParameter parameter in cmdParms)
{
if ((parameter.Direction == ParameterDirection.InputOutput || parameter.Direction == ParameterDirection.Input) &&
(parameter.Value == null))
{
parameter.Value = DBNull.Value;
}
cmd.Parameters.Add(parameter);
}
}
}

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。