c#连接SQL Server 数据库

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 Add(Model.test 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 = model.testId;
            parameters[1].Value = model.testName;
            parameters[2].Value = model.testTyp;
            parameters[3].Value = model.testTime;

            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)
                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);
                }
            }
        }

 

转载于:https://www.cnblogs.com/guo-pc31/p/3651576.html