c#和SqlServer数据库连接

代码如下，请看注释

using System;
using System.Data;
using System.Data.SqlClient;

namespace CSharpTest
{
    class Program
    {
        static void Main(string[] args)
        {
            Express ex = new Express();
            //将Student表内加一个元组
            string t = "insert into Student values('204361128', '陈力', '男', '14', 'CS')";
            //将Student表内数据选出来
            string t2 = "select * from Student";

            //Console.WriteLine(ex.NonQuery(t));//更改表内数据
            DataSet ds = ex.Query(t2);
            DataTable dTab = ds.Tables[0];
            foreach (DataColumn item in dTab.Columns)
            {
                Console.WriteLine(item.ColumnName);//查询表内属性
            }
            foreach (DataRow item in dTab.Rows)//遍历行
            {
                foreach (DataColumn tt in dTab.Columns)//把表内当前行的每个属性的值输出
                {
                    Console.Write(item[tt] + " ");
                }
                Console.WriteLine();
            }

        }
    }
    class Express
    {
        //本地连接一个本地数据库中一个叫s204361124的数据库
        public string connString = "Data Source=.;Initial Catalog=s204361124;Integrated Security=TRUE";
        public SqlConnection conn;
        //数据库的增删操作
        public int NonQuery(string sql)
        {
            conn = new SqlConnection(connString);
            int a = -1;
            try
            {
                conn.Open();
                SqlCommand cmd = new SqlCommand(sql,conn);
                a = cmd.ExecuteNonQuery();
            }
            catch
            {

                
            }
            finally
            {
                if (conn.State==ConnectionState.Open)
                {
                    Console.WriteLine("有问题");
                    conn.Close();
                }
            }
            return a;
        }
        //数据库的查询操作
        public DataSet Query(string sql)
        {
            conn = new SqlConnection(connString);
            DataSet ds = new DataSet();
            try
            {
                conn.Open();      //打开数据库
                SqlDataAdapter adp = new SqlDataAdapter(sql, conn);
                adp.Fill(ds);
            }
            catch
            {

            }
            finally
            {
                if (conn.State == ConnectionState.Open)
                    conn.Close();      //关闭数据库
            }
            return ds;
        }
    }
}

具体参考---->此处

使用SqlServer连接处理二进制数据

1.首先将要存的数据转化成二进制，再存入数据库，具体操作如下

using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Text.RegularExpressions;
using System.Runtime.Serialization.Formatters.Binary;
using System.Runtime.Serialization;
using System.IO;

namespace Connect2
{
    class DataMgr
    {
        public SqlConnection sqlConn;
        public static DataMgr instance;

        public DataMgr()
        {
            instance = this;
            Connect();
        }
        public void Connect()
        {
            string connStr = "Data Source=.;Initial Catalog=Internet;Integrated Security=TRUE";
            sqlConn = new SqlConnection(connStr);
            try
            {
                //打开数据库
                sqlConn.Open();
            }
            catch (Exception e)
            {
                Console.WriteLine("[DataMgr]Connect "+e.Message);
                return;
            }
        }
        //正则表达式判断是否符合注册标准
        public bool IsSafeStr(string str)
        {
            return !Regex.IsMatch(str, @"[-|;|,|\/|\(|\)|\[|\]|\}|\{|%|@|\*|!|\']");
        }
        private bool CanRegister(string id)
        {
            if (!IsSafeStr(id))
            {
                return false;
            }
            string cmdStr = string.Format("select * from Iuser where id='{0}'",id);
            try
            {
                SqlDataAdapter adap = new SqlDataAdapter(cmdStr,sqlConn);
                DataSet ds = new DataSet();
                adap.Fill(ds);
                DataTable dTable = ds.Tables[0]; 
                //查到了数据就返回不可注册，否则就可以注册
                if (dTable.Rows.Count>0)
                {
                    return false;
                }
                return true;
            }
            catch (Exception e)
            {

                Console.WriteLine("[DataMgr]IsSafeFail "+e.Message);
                return false;
            }
        }

        public bool Register(string id,string pw)
        {
            //防止sql注入，也就是非正常用户名
            if (!IsSafeStr(id)||!IsSafeStr(pw))
            {
                return false;
            }
            if (!CanRegister(id))
            {
                Console.WriteLine("[DataMgr]Registe!canRegister ");
                return false;
            }
            //如果条件全部满足则允许注册角色
            string cmdStr = string.Format("insert into Iuser values('{0}','{1}')",id,pw);
            SqlCommand sqlcmd = new SqlCommand(cmdStr,sqlConn);
            try
            {
                sqlcmd.ExecuteNonQuery();
                return true;
            }
            catch (Exception e)
            {
                Console.WriteLine("[DataMgr]Register "+e.Message);
                return false;
            }
        }

        public bool CreatePlayer(string id)
        {
            //防止sql注入
            if (!IsSafeStr(id))
            {
                return false;
            }
            IFormatter formatter=new BinaryFormatter();
            MemoryStream stream = new MemoryStream();
            PlayerData pData = new PlayerData();
            try
            {
                formatter.Serialize(stream,pData);
            }
            catch (Exception e)
            {
                Console.WriteLine("[DataMgr]CreatePlayer 序列化失败 "+e.Message);
            }
            //将其转化的流转为字符数组
            byte[] bytes = stream.ToArray();
            string cmdstr = string.Format("insert into player values('{0}',@data)",id);
            try
            {
                SqlCommand sqlCmd = new SqlCommand(cmdstr,sqlConn);
                sqlCmd.Parameters.Add("@data",SqlDbType.VarBinary);
                sqlCmd.Parameters[0].Value = bytes;
                sqlCmd.ExecuteNonQuery();
                return true;
            }
            catch (Exception e)
            {
                Console.WriteLine("[DataMgr]CreatePlayer 加入数据库 "+e.Message);
                return false;
            }
        }

        public bool CheckPassWord(string id,string pw)
        {
            //防止sql注入，也就是非正常用户名
            if (!IsSafeStr(id) || !IsSafeStr(pw))
            {
                return false;
            }
            //使数据库里的text类型与其兼容
            string cmdStr = string.Format("select * from Iuser where id='{0}' and cast(pw as varchar(2000))='{1}'", id,pw);
            try
            {
                SqlDataAdapter adap = new SqlDataAdapter(cmdStr, sqlConn);
                DataSet ds = new DataSet();
                adap.Fill(ds);
                DataTable dTable = ds.Tables[0];
                //查到了数据就返回验证成功，否则就失败
                if (dTable.Rows.Count > 0)
                {
                    return true;
                }
                return false;
            }
            catch (Exception e)
            {

                Console.WriteLine("[DataMgr]CheckPasswordFail " + e.Message);
                return false;
            }
        }

        public PlayerData GetPlayerData(string id)
        {
            PlayerData pData = null;
            byte[] buffer = new byte[1];
            if (!IsSafeStr(id))
            {
                return pData;
            }
            string cmdStr = string.Format("select * from player where id ='{0}'", id);
            try
            {
                SqlDataAdapter sqlAda = new SqlDataAdapter(cmdStr,sqlConn);
                DataSet dSet = new DataSet();
                sqlAda.Fill(dSet);
                DataTable dTable = dSet.Tables[0];
                if (dTable.Rows.Count<=0)
                {
                    return pData;
                }
                DataRow dRow = dTable.Rows[0];
                int len = dRow[1].ToString().Length;
                buffer = new byte[len];
                ///
                ///这地方很有可能出错后面改一下
                ///
                buffer = (byte[])dRow[1];

            }
            catch (Exception e)
            {
                Console.WriteLine("[DataMgr]GetPlayerFile转化成字节数组失败 "+e.Message);
                return pData;
            }
            MemoryStream stream = new MemoryStream(buffer);
            try
            {
                BinaryFormatter formatter = new BinaryFormatter();
                pData = (PlayerData)formatter.Deserialize(stream);
                return pData;
            }
            catch (Exception e)
            {
                Console.WriteLine("[DataMgr]GetPlayerFile反序列化失败 " + e.Message);
                return pData;
            }
        }

        public bool SavePlayer(Player player)
        {
            string id = player.id;
            PlayerData playerData = player.pData;

            IFormatter formatter = new BinaryFormatter();
            MemoryStream stream = new MemoryStream();
            try
            {
                formatter.Serialize(stream,playerData);
            }
            catch (Exception e)
            {
                Console.WriteLine("[DataMgr]SavePlayerFile序列化失败 " + e.Message);
                return false;
            }
            byte[] buffer = stream.ToArray();
            string cmdStr = string.Format("update player set playerData =@data where id = '{0}'",id);
            SqlCommand cmd = new SqlCommand(cmdStr,sqlConn);
            cmd.Parameters.Add("@data",SqlDbType.VarBinary);
            cmd.Parameters[0].Value = buffer;
            try
            {
                cmd.ExecuteNonQuery();
                return true;
            }
            catch (Exception e)
            {
                Console.WriteLine("[DataMgr]SavePlayerFile保存失败 " + e.Message);
                return false;
            }
        }
    }
}