高手指点一下C#的增删改查语法

Question

请高手告诉一下 C#中 增删改查的 语法结构，谢谢

Answer 1

 public class AccessMethod
    {     static string StrConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + HttpContext.Current.Server.MapPath("~/db/") + "lyk.mdb";
        // static string StrConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=lyk.mdb";
       // static string StrConn = System.Configuration.ConfigurationManager.ConnectionStrings["SQLDATA"].ToString();
        OleDbConnection conn = new OleDbConnection(StrConn);        public void EXECSQL(string sql)
        {
            OleDbCommand cmd = new OleDbCommand(sql, conn);
            conn.Open();
            cmd.ExecuteNonQuery();
            conn.Close();
        }        public DataSet EXECSQLSS(string StrSelect)
        {
            DataSet ds = new DataSet();
           // string StrSelect = "select top 6  * from AccessInfo where Classify='" + Classify + "' order by Cid desc";
            OleDbDataAdapter adp = new OleDbDataAdapter(StrSelect, conn);
            adp.Fill(ds);            return ds;
        }
        public AccessInfo EXECSQLS(string a)
        {
            AccessInfo info = new AccessInfo();           // string a = "select * from AccessInfo where CID =" + aid;
            conn.Open();
            OleDbCommand cmd = new OleDbCommand(a, conn);
            OleDbDataReader read = cmd.ExecuteReader();
            while (read.Read())
            {
                info.CName = read.GetValue(1).ToString();
                info.CTime = read.GetValue(2).ToString();
                info.Contents = read.GetValue(3).ToString();
                info.Classify = read.GetValue(4).ToString();
                try
                {
                    info.Model = read.GetValue(5).ToString();
                    info.Material = read.GetValue(6).ToString();
                    info.Num = read.GetValue(7).ToString();
                }
                catch { }            }
            conn.Close();
            return info;
        }        public void insertIntAccessInfo(AccessInfo info)
        {
            string StrInsert = "insert into AccessInfo(CName,CTime,Contents,Classify,Model,Material,Num) values('" + info.CName + "','" + info.CTime + "','" + info.Contents + "','" + info.Classify + "','" + info.Model + "','" + info.Material + "','" + info.Num + "')";
            OleDbCommand cmd = new OleDbCommand(StrInsert, conn);
            conn.Open();
            cmd.ExecuteNonQuery();
            conn.Close();
        }
        /// <summary>
        /// 从AccessInfo数据表中删除数据
        /// </summary>
        /// <param name="info"></param>
        /// <returns></returns>
        public void deleteFromAccessInfo(int info)
        {            string StrDelete = "delete from AccessInfo where CID=" + info;
            OleDbCommand cmd = new OleDbCommand(StrDelete, conn);
            conn.Open();
            cmd.ExecuteNonQuery();
            conn.Close();
        }           public void modifyAccessInfo(AccessInfo info)
        {            string StrModify = "update AccessInfo set CName='" + info.CName + "',CTime='" + info.CTime + "',Contents='" + info.Contents + "',Classify='" + info.Classify + "',Model='" + info.Model + "',Material='" + info.Material  + "',Num='" + info.Num + "' where Cid=" + info.CID;
            OleDbCommand cmd = new OleDbCommand(StrModify, conn);
            conn.Open();
            cmd.ExecuteNonQuery();
            conn.Close();
        }        /// <summary>
        /// 从数据表AccessInfo中查找所有的记录
        /// </summary>
        /// <returns></returns>
        public DataSet selectAllFromAccessInfo(string Classify)
        {
            DataSet ds = new DataSet();
            string StrSelect = "select * from AccessInfo where Classify='" + Classify + "' order by CID desc";
            OleDbDataAdapter adp = new OleDbDataAdapter(StrSelect, conn);
            adp.Fill(ds);            return ds;
        }        /// <summary>
        /// 从数据表AccessInfo中查找所有的记录
        /// </summary>
        /// <returns></returns>
        public DataSet selectTopAllFromAccessInfo(string Classify, int how)
        {
            DataSet ds = new DataSet();
            string StrSelect = null;
            if (how > 0)
            {
                StrSelect = "select top 10  * from AccessInfo where Classify='" + Classify + "' and CID not in ( select top " + how + "  CID from AccessInfo where Classify='" + Classify + "' order by Cid desc ) order by CID desc";
            }
            else
            {
                StrSelect = "select top 10 * from AccessInfo where Classify='" + Classify + "' order by CID desc";
            }
            OleDbDataAdapter adp = new OleDbDataAdapter(StrSelect, conn);
            adp.Fill(ds);            return ds;
        }        public DataSet selectTopSixFromAccessInfo(string Classify, int how)
        {
            DataSet ds = new DataSet();
            string StrSelect = null;
            if (how > 0)
            {
                StrSelect = "select top 6  * from AccessInfo where Classify='" + Classify + "' and CID not in ( select top " + how + "  CID from AccessInfo where Classify='" + Classify + "' order by Cid desc ) order by CID desc";
            }
            else
            {
                StrSelect = "select top 6  * from AccessInfo where Classify='" + Classify + "' order by CID desc";
            }
            OleDbDataAdapter adp = new OleDbDataAdapter(StrSelect, conn);
            adp.Fill(ds);            return ds;
        }        public AccessInfo GetByID(int aid)
        {
            AccessInfo info = new AccessInfo();            string a = "select * from AccessInfo where CID =" + aid;
            conn.Open();
            OleDbCommand cmd = new OleDbCommand(a, conn);
            OleDbDataReader read = cmd.ExecuteReader();
            while (read.Read())
            {
                info.CName  = read.GetValue(1).ToString();
                info.CTime = read.GetValue(2).ToString();
                info.Contents = read.GetValue(3).ToString();
                info.Classify  = read.GetValue(4).ToString();
                try
                {
                    info.Model = read.GetValue(5).ToString();
                    info.Material  = read.GetValue(6).ToString();
                    info.Num = read.GetValue(7).ToString();
                }
                catch { }            }
            conn.Close();
            return info;
        }        public AccessInfo ZPTopOne()
        {
            AccessInfo info = new AccessInfo();            string a = "select top 1  * from AccessInfo where Classify='10' order by Cid desc";
            conn.Open();
            OleDbCommand cmd = new OleDbCommand(a, conn);
            OleDbDataReader read = cmd.ExecuteReader();
            while (read.Read())
            {
                info.CName  = read.GetValue(1).ToString();
                info.CTime = read.GetValue(2).ToString();
                info.Contents = read.GetValue(3).ToString();
                info.Classify  = read.GetValue(4).ToString();            }
            conn.Close();
            return info;
        }
        public AccessInfo GKTopOne()
        {
            AccessInfo info = new AccessInfo();            string a = "select top 1  * from AccessInfo where Classify='1' order by Cid desc";
            conn.Open();
            OleDbCommand cmd = new OleDbCommand(a, conn);
            OleDbDataReader read = cmd.ExecuteReader();
            while (read.Read())
            {
                info.CName = read.GetValue(1).ToString();
                info.CTime = read.GetValue(2).ToString();
                info.Contents = read.GetValue(3).ToString();
                info.Classify = read.GetValue(4).ToString();            }
            conn.Close();
            return info;
        }        public AccessInfo WHTopOne()
        {
            AccessInfo info = new AccessInfo();            string a = "select top 1  * from AccessInfo where Classify='20' order by Cid desc";
            conn.Open();
            OleDbCommand cmd = new OleDbCommand(a, conn);
            OleDbDataReader read = cmd.ExecuteReader();
            while (read.Read())
            {
                info.CName = read.GetValue(1).ToString();
                info.CTime = read.GetValue(2).ToString();
                info.Contents = read.GetValue(3).ToString();
                info.Classify = read.GetValue(4).ToString();            }
            conn.Close();
            return info;
        }
        public DataSet selectTopAllFromAccessInfo(string Classify)
        {
            DataSet ds = new DataSet();
            string StrSelect = "select top 6  * from AccessInfo where Classify='" + Classify + "' order by Cid desc";
            OleDbDataAdapter adp = new OleDbDataAdapter(StrSelect, conn);
            adp.Fill(ds);            return ds;
        }        public DataSet Set()
        {
            DataSet ds = new DataSet();
          //  string StrSelect = "select * from AccessInfo where Classify='7' or Classify='8' or Classify='12' or Classify='11'or Classify='13' or Classify='18' order by Cid desc";
             string StrSelect = "select * from AccessInfo where Material='lyk' order by Cid desc"; 
            OleDbDataAdapter adp = new OleDbDataAdapter(StrSelect, conn);
            adp.Fill(ds);            return ds;
        }

Answer 2

这个是数据库Sql语句的事情吧
只要你的sql语句正确，怎么去执行sql语句看看书吧

Answer 3

我的意思就是。。 C# 连接 SQL  用到的 增加 删除 修改 查询 的语法 .... 

Answer 4

连接到sql之后去看下sql的增删查改

Answer 5

   这个简单啊。
   写好连接语句后。
  增加、删除、修改可以用Command.
  查询可以用Dataset啊。其实方法很多。要看你怎么用了。 

Answer 6

首先是先连接数据库，之后打开它就可以做相关的操作了
这个方法是从数据库中查找数据的方法：
        public DataSet query(string sql)
        { DataSet ds = new DataSet();//DataSet是表的集合
            OleDbDataAdapter da = new OleDbDataAdapter(sql,conn);//从数据库中查询
            da.Fill(ds);//将数据填充到DataSet
            connClose();//关闭连接
            return ds;//返回结果
        }
其他都也都差不多的了，慢慢学。

Answer 7

你学好SQL语法便可，C#做的事情只是将你的SQL递上去。

Answer 8

ado.net的操作，写过文章，提供了完整代码，和存储过程，楼主可以参考一下，
http://blog.csdn.net/zhzuo/archive/2004/08/06/67016.aspx
http://blog.csdn.net/zhzuo/archive/2004/08/06/67037.aspx
http://blog.csdn.net/zhzuo/archive/2005/01/03/238273.aspx

Answer 9

给你个BaseServicesusing System;
using System.Collections.Generic;
using System.Text;
using System.Configuration;
using System.Data.SqlClient;
using System.Data;
namespace Divinity_OA.DALSqlFactory
{
    public delegate void DataRender<T>(IList<T> list, IDataReader reader);
    public class BaseServers
    {        public static readonly string connString = ConfigurationManager.ConnectionStrings["ConString"].ConnectionString;
        #region  sqlCommand
        /// <summary>
        /// CreateCommand  获得CMD
        /// </summary>
        /// <param name="sql">sql语句</param>
        /// <param name="cmdType">操作类型</param>
        /// <param name="param">参数集合</param>
        /// <returns>sqlCommand</returns>
        private static SqlCommand CreateCommand(string sql, CommandType cmdType, params SqlParameter[] param)
        {
            try
            {
                SqlConnection conn = new SqlConnection(connString);
                conn.Open();
                SqlCommand cmd = new SqlCommand(sql, conn);
                cmd.CommandType = cmdType;
                cmd.Parameters.AddRange(param);
                return cmd;
            }
            catch (Exception ex)
            {            }
            return null;
        }        #endregion
        #region  ExecuteNonQuery, ExecuteProc ,ExecuteNonQuery        /// <summary>
        /// ExecuteNonQuery
        /// </summary>
        /// <param name="sql">sql语句</param>
        /// <param name="cmdType">操作类型</param>
        /// <param name="param">参数集合</param>
        /// <returns>int</returns>
        private static int ExecuteNonQuery(string sql, CommandType cmdType, params SqlParameter[] param)
        {
            SqlCommand cmd = CreateCommand(sql, cmdType, param);
            int count = 0;
            try
            {
                count = cmd.ExecuteNonQuery();
            }
            catch (Exception)
            {
                throw;
            }
            finally
            {
                cmd.Connection.Close();
                cmd.Connection.Dispose();
            }
            return count;
        }
        /// <summary>
        /// ExecuteProc   执行存储过程
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="param"></param>
        /// <returns></returns>
        public static int ExecuteProc(string proName, params SqlParameter[] param)
        {
            return ExecuteNonQuery(proName, CommandType.StoredProcedure, param);
        }        /// <summary>
        /// ExecuteNonQuery   重载 
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="param"></param>
        /// <returns></returns>
        public static int ExecuteNonQuery(string sql, params SqlParameter[] param)
        {
            return ExecuteNonQuery(sql, CommandType.Text, param);
        }
        #endregion
        #region SqlDataReader, GetReaderProc,SqlDataReader
        /// <summary>
        /// SqlDataReader  执行查询方法
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="cmdType"></param>
        /// <param name="param"></param>
        /// <returns></returns>
        private static SqlDataReader GetReader(string sql, CommandType cmdType, params SqlParameter[] param)
        {
            SqlCommand cmd = CreateCommand(sql, cmdType, param);            SqlDataReader reader = null;
            try
            {
                reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
            }
            catch (Exception)
            {                throw;
            }
            return reader;
        }        /// <summary>
        /// SqlDataReader
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="param"></param>
        /// <returns></returns>
        public static SqlDataReader GetReader(string sql, params SqlParameter[] param)
        {
            return GetReader(sql, CommandType.Text, param);
        }
        /// <summary>
        /// GetReaderProc  根据参数不同判断操作类型
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="param"></param>
        /// <returns></returns>
        public static SqlDataReader GetReaderProc(string sql, params SqlParameter[] param)
        {
            return GetReader(sql, CommandType.StoredProcedure, param);
        }        #endregion        #region ExecuteScalar<T>
        /// <summary>
        /// ExecuteScalar<T>泛型聚合函数
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="sql"></param>
        /// <param name="param"></param>
        /// <returns></returns>
        public static T ExecuteScalar<T>(string sql, params SqlParameter[] param)
        {
            object value = null;
            SqlCommand cmd = CreateCommand(sql, CommandType.Text, param);
            try
            {
                value = cmd.ExecuteScalar();
            }
            catch (Exception)
            {                throw;
            }
            finally
            {
                cmd.Connection.Close();
                cmd.Connection.Dispose();
            }
            return (T)value;
        }        #endregion
        #region ExecuteReader
        /// <summary>
        /// ExecuteReader<T>
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="sql"></param>
        /// <param name="render"></param>
        /// <param name="param"></param>
        /// <returns></returns>
        public static IList<T> ExecuteReader<T>(string sql, DataRender<T> render, params SqlParameter[] param)
        {            using (SqlDataReader reader = GetReader(sql, param))
            {
                IList<T> list = new List<T>();
                render(list, reader);
                reader.Close();
                reader.Dispose();
                return list;
            }
        }
        #endregion        #region ExecuteTransaction
        /// <summary>
        /// ExecuteTransaction   事务
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="param"></param>
        /// <returns></returns>
        public static bool ExecuteTransaction(IList<string> sql, IList<SqlParameter[]> param)
        {            SqlConnection conn = new SqlConnection(connString);
            SqlTransaction trans = conn.BeginTransaction();            try
            {
                conn.Open();
                for (int i = 0; i < sql.Count && i < param.Count; i++)
                {
                    SqlCommand cmd = new SqlCommand(sql[i], conn);
                    cmd.Transaction = trans;//???
                    cmd.Parameters.AddRange(param[i]);
                    cmd.ExecuteNonQuery();
                }
                trans.Commit();
                return true;
            }
            catch (Exception)
            {
                trans.Rollback();
            }
            finally
            {
                conn.Close();
                conn.Dispose();
            }
            return false;
        }
        #endregion
    }
}