风雨悠扬
2015-09-14 23:47
采纳率: 100%
浏览 6.4k

求一个c#连接sqlserver数据库的完整实例

求一个c#连接sqlserver数据库的完整实例,谢谢!我的代码总是有问题,请大神帮帮忙

  • 写回答
  • 好问题 提建议
  • 关注问题
  • 收藏
  • 邀请回答

5条回答 默认 最新

  • agjaisdjfo 2015-09-15 00:44
    已采纳

    连接就使用SqlConnection类连接,Connection.Open()打开数据库连接。
    使用SqlCommand类操作,有三个属性:(1)Command.Connection。这个就是使用刚刚打开的Connection类。(2)Command.CommandText,设置SQL语言。(3)Command.CommandType,设置CommandText的属性。
    最后执行Command。完成操作。
    发个例子吧:
    图片说明

    已采纳该答案
    评论
    解决 无用
    打赏 举报
  • 宝_爸 2015-09-15 00:06

    你的代码是什么问题呢?
    网上很多例子啊,前提是你的sql server数据库也要设置好才行。

    Beginners guide to accessing SQL Server through C#

    评论
    解决 无用
    打赏 举报
  • 宝_爸 2015-09-15 00:10

    你的代码是什么问题呢?
    网上很多例子啊,前提是你的sql server数据库也要设置好才行。

    Beginners guide to accessing SQL Server through C#

    评论
    解决 无用
    打赏 举报
  • threenewbee 2015-09-15 00:11

    http://www.cnblogs.com/bluestorm/p/3368466.html
    http://blog.csdn.net/kkkkkxiaofei/article/details/7904569
    参考下

    关键是你的连接字符串和 sql server 的配置是否正确。

    评论
    解决 无用
    打赏 举报
  • 守枫竹清 2015-09-15 00:45

    config 配置


    using System;
    using System.Collections.Generic;
    using System.Linq;
    using System.Web;
    using System.Data;
    using System.Configuration;
    using System.Data.SqlClient;
    using System.Text;

    namespace MonitorServiceClass.SQLService
    {
    public enum Conditional
    {
    AND,
    OR
    }

    public class SqlHelper
    {
       public  static string myConnection = ConfigurationManager.ConnectionStrings["conn"].ToString();
    
        public static string Error = null;
        public SqlHelper()
        {
            //InitConnection();
        }
    
        public void  InitConnection()
        {
               try
               {
                   SqlConnection conn = new SqlConnection(myConnection);
                   conn.Open();
               }
               catch (Exception e)
               {
                   Console.WriteLine("concetion is fail " +e.Message );
               }
        }
    
    
        /// <summary>
        /// 插入一条数据
        /// </summary>
        /// <param name="tableName">表名</param>
        /// <param name="dataMap">数据(列名,值)</param>
        /// <returns></returns>
        public static  bool InsertData(string tableName, Dictionary<string, string> dataMap)
        {
            Error = null;
            bool isOk = false;
            StringBuilder myStr = new StringBuilder();
    
            try
            {
                myStr.Append("insert into ");
                myStr.Append(tableName);
                myStr.Append("  (");
    
                foreach (KeyValuePair<string,string> kvp in dataMap)
                {
                    myStr.Append(kvp.Key);
                    myStr.Append(",");
                }
                myStr.Remove(myStr.Length - 1, 1);
                myStr.Append(")");
    
                myStr.Append(" values ");
                myStr.Append(" (");
                foreach (KeyValuePair<string,string> kvp in dataMap)
                {
                    myStr.Append("'");
                    myStr.Append(kvp.Value);
                    myStr.Append("'");
                    myStr.Append(",");
                }
                myStr.Remove(myStr.Length - 1, 1);
    
                myStr.Append(")");
    
                int num = ExecuteNonQuery(myStr.ToString());
                if (num > 0)
                {
                    isOk = true;
    
    
                }
    
            }
            catch (Exception e)
            {
                Error = e.Message.ToString();
            }
            finally
            {
                myStr = null;
            }
            return isOk;
        }
    
     /// <summary>
     /// 更新表中的数据
     /// </summary>
     /// <param name="tableName">表名</param>
     /// <param name="dataMap">数据(列名,值)</param>
     /// <param name="selectKey">选择的列名</param>
     /// <param name="selectValue">选择的值</param>
     /// <returns>判断是否更新数据成功</returns>
        public static bool UpdateData(string tableName, Dictionary<string, string> dataMap,string selectKey,string selectValue)
        {
            Error = null;
            bool isOk = false;
            StringBuilder myStr = new StringBuilder();
    
            try
            {
                myStr.Append("update ");
                myStr.Append(tableName);
                myStr.Append(" set ");
                foreach(KeyValuePair<string,string> kvp in dataMap)
                {
                    myStr.Append(kvp.Key);
                    myStr.Append(" = ");
                    myStr.Append("'");
                    myStr.Append(kvp.Value);
                    myStr.Append("',");
                }
                myStr.Remove(myStr.Length - 1, 1);
    
                myStr.Append(" where ");
                myStr.Append(selectKey);
                myStr.Append(" = ");
                myStr.Append("'");
                myStr.Append(selectValue);
                myStr.Append("'");
    
                int num = ExecuteNonQuery(myStr.ToString());
                if(num > 0)
                    isOk = true;
    
    
            }
            catch (Exception e)
            {
                Error = e.Message.ToString();
            }
            finally
            {
                myStr = null;
            }
            return isOk;
    
        }
    
        /// <summary>
        /// 删除指定的数据
        /// </summary>
        /// <param name="tableName">表名</param>
        /// <param name="dataMap">数据(列名,值)</param>
        /// <param name="operate">逻辑操作符</param>
        /// <returns></returns>
        public static bool DeleteData(string tableName, Dictionary<string,string> dataMap,Conditional operate)
        {
            Error = null;
            bool isOk = false;
            StringBuilder myStr = new StringBuilder();
    
            try
            {
                myStr.Append("delete from ");
                myStr.Append(tableName);
                myStr.Append(" where ");
                if (operate == Conditional.AND)
                {
                    foreach (KeyValuePair<string, string> kvp in dataMap)
                    {
                        myStr.Append(kvp.Key);
                        myStr.Append(" = ");
                        myStr.Append("'");
                        myStr.Append(kvp.Value);
                        myStr.Append("'");
                        myStr.Append(" and ");
                    }
                    myStr.Remove(myStr.Length - 4, 4);
                }
                else
                {
                    foreach (KeyValuePair<string, string> kvp in dataMap)
                    {
                        myStr.Append(kvp.Key);
                        myStr.Append(" = ");
                        myStr.Append("'");
                        myStr.Append(kvp.Value);
                        myStr.Append("'");
                        myStr.Append(" or ");
                    }
                    myStr.Remove(myStr.Length - 3, 3);
                }
    
    
    
                int num = ExecuteNonQuery(myStr.ToString());
                if (num > 0)
                    isOk = true;
    
    
            }
            catch (Exception e)
            {
                Error = e.Message.ToString();
            }
            finally
            {
                myStr = null;
            }
            return isOk;
        }
    
    
        /// <summary>
        /// 按sql查询
        /// </summary>
        /// <param name="sqlString"></param>
        /// <returns></returns>
        public static DataTable QueryBySql(string sqlString)
        {
            try
            {
                return ExecuteTable(sqlString);
            }
            catch (SqlException ex)
            {
                Error = ex.Message.ToString();
                return null;
            }
        }
    
        public static DataTable ExecuteTable(string cmdText)
        {
            using (SqlConnection conn = new SqlConnection(myConnection))
            {
                conn.Open();
                SqlDataAdapter adp = new SqlDataAdapter(cmdText, conn);
                DataSet ds = new DataSet();
                adp.Fill(ds);
                conn.Close();
                return ds.Tables[0];
            }
        }
    
    
        public static void UpdateDataSet(DataTable dt, string strSql)
        {
            using (SqlConnection conn = new SqlConnection(myConnection))
            {
                try
                {
                    conn.Open();
                    SqlDataAdapter command = new SqlDataAdapter(strSql, conn);
                    SqlCommandBuilder cmd = new SqlCommandBuilder(command);
                    command.Update(dt);
                }
                catch (SqlException ex)
                {
                    Error = ex.Message.ToString();
                }
                finally
                {
                    if (conn.State != ConnectionState.Closed)
                    {
                        conn.Close();
                    }
                }
            }
        }
    
        public static void InsertDataSet(DataTable dt, string strSql)
        {
            using (SqlConnection conn = new SqlConnection(myConnection))
            {
                try
                {
                    conn.Open();
                    SqlDataAdapter adapter = new SqlDataAdapter(strSql, conn);
                    SqlCommandBuilder commandBuilder = new SqlCommandBuilder(adapter);
                    adapter.InsertCommand = commandBuilder.GetInsertCommand();
                    adapter.Update(dt);
                    dt.AcceptChanges();
                }
                catch (SqlException ex)
                {
                    Error = ex.Message.ToString();
                }
                finally
                {
                    if (conn.State != ConnectionState.Closed)
                    {
                        conn.Close();
                    }
                }
            }
    
        }
    
        /// <summary>
        /// 用于执行 Update,Insert,Delete语句时候, 返回操作影响的行数
        /// </summary>
        /// <param name="sqlString">sql执行语句</param>
        /// <returns>返回受影响的行数</returns>
        public static int ExecuteNonQuery(string sqlString)
        {
            Error = null;
            using (SqlConnection conn = new SqlConnection(myConnection))
            {
                int rows = 0;
                using( SqlCommand command = new SqlCommand(sqlString, conn))
                try
                {
                    conn.Open();
                    rows = command.ExecuteNonQuery();
                    return rows;
                }
                catch (SqlException ex)
                {
                    Error = ex.Message.ToString();
    
                }
                finally
                {
                    if (conn.State != ConnectionState.Closed)
                    {
                        conn.Close();
                    }
                }
                return rows;
    
            }
        }
    
        /// <summary>
        /// 用于执行其它sql语句操作时候,返回DataSet 数据
        /// </summary>
        /// <param name="sqlString">执行的Sql语句</param>
        /// <returns>返回的DataSet</returns>
        public static DataSet ExecuteQuery(string sqlString)
        {
            Error = null;
            using (SqlConnection conn = new SqlConnection(myConnection))
            {
                DataSet ds = new DataSet();
                try
                {
                    conn.Open();
                    SqlDataAdapter adp = new SqlDataAdapter(sqlString, conn);
                    adp.Fill(ds, "ds");
                    return ds;
                }
                catch (SqlException ex)
                {
                    Error = ex.Message.ToString();
                }
                finally
                {
                    if (conn.State != ConnectionState.Closed)
                    {
                        conn.Close();
                    }
                }
                return null;
    
            }
        }
    
        /// <summary>
        /// 对于其它操作的返回值,通过转换来获取受影响的行数来判断是否操作成功
        /// </summary>
        /// <param name="ds">DataSet</param>
        /// <returns>返回受影响的行数</returns>
        public static int AffectLineAmount(DataSet ds)
        {
            if (ds == null)
                return 0;
            else
            {
                int count = ds.Tables[0].Rows.Count;
                return count;
            }
        }
    
    
    
    
    
    
    
    
    
    }
    

    }

    评论
    解决 无用
    打赏 举报

相关推荐 更多相似问题