C# 连接数据库

最新推荐文章于 2024-05-03 23:50:55 发布

weixin_43602710

最新推荐文章于 2024-05-03 23:50:55 发布

阅读量118

点赞数

分类专栏： C# Basic

本文链接：https://blog.csdn.net/weixin_43602710/article/details/85165932

版权

C# Basic 专栏收录该内容

8 篇文章 0 订阅

订阅专栏

  private void ExecuteSQLScalar()
        {
            //sqlconnect must be closed after executing the SQL
            /*To run the SQL, must have below elements 
                1. SqlConnection
                2. SqlCommand
                3. Execution Method i.e ExecuteScalar, ExecuteScalar
            */
            using (conn = new SqlConnection(connectionString))
            {
                SqlCommand cmd = new SqlCommand();
                string selectCommand1 = "select top 5 * from[issue_regulation] order by issue_id desc";
                string selectCommand2 = "select  * from [issue_regulation] where party_to_withhold_id =1 order by issue_id desc";
                string updateCommand = "update [issue_regulation] set last_modified_at = '30/09/2018' where issue_regulation_id = 1651";

                cmd.Connection = conn;
                conn.Open();

                //ExecuteScalar returns first row firt column
                cmd = new SqlCommand(selectCommand1, conn);
                var result1 = cmd.ExecuteScalar();

                //ExecuteNonQuery returns the effected record count
                cmd = new SqlCommand(updateCommand, conn);
                var result2 = cmd.ExecuteNonQuery();

                //ExecuteNonQuery run the select SQL will return -1
                cmd = new SqlCommand(selectCommand1, conn);
                var result3 = cmd.ExecuteNonQuery();

                //ExecuteReader must be closed after execution
                cmd = new SqlCommand(selectCommand1, conn);
                SqlDataReader reader = cmd.ExecuteReader();

                if (reader.HasRows)
                {
                    string msg = string.Empty;
                    while (reader.Read())
                    {
                        var oo = reader.GetValue(0);
                    }
                }
                reader.Close();

                //SqlDataAdapter
                DataSet ds = new DataSet();
                DataTable dt = new DataTable();
                SqlDataAdapter adapter = new SqlDataAdapter();
                cmd = new SqlCommand(selectCommand1, conn);
                adapter.SelectCommand = cmd;
                adapter.Fill(ds, "MyDt1");

                cmd = new SqlCommand(selectCommand2, conn);
                adapter.SelectCommand = cmd;
                adapter.FillSchema(ds, SchemaType.Mapped, "MyDt2");
                adapter.Fill(ds, "MyDt2");

                object[] keys = { "10112546", "1"};
                DataRow dr = ds.Tables["MyDt2"].Rows.Find(keys);
                dr["last_modified_at"] = "29/08/2018";

                SqlCommandBuilder builder = new SqlCommandBuilder(adapter);
                adapter.Update(ds, "MyDt2");


                //Playaround
                DataSet ds1 = new DataSet();
                DataTable dt1 = new DataTable();
                SqlDataAdapter adapter1 = new SqlDataAdapter();
                cmd = new SqlCommand(selectCommand1, conn);
                adapter.Fill(ds, "MyDt3");
            }