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

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

5个回答

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

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

Beginners guide to accessing SQL Server through C#

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

Beginners guide to accessing SQL Server through C#

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

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

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









}

}

Csdn user default icon
上传中...
上传图片
插入图片
抄袭、复制答案,以达到刷声望分或其他目的的行为,在CSDN问答是严格禁止的,一经发现立刻封号。是时候展现真正的技术了!