ado.net之c#基础增加/修改/删除/查询

妖狐艹你老母 2022-12-11 09:29 154阅读 0赞

class AdoDemoD
        { 
            static string strConn = @"data source=192.168.0.237;initial catalog=XYWMS;user id=sa;multipleactiveresultsets=True;application name=EntityFramework" providerName="System.Data.SqlClient" ";
            static void Main(string[] args)
            { 
                //InsertData();//1.1插入新数据练习
                //InsertDataParameters();//1.2插入新数据练习,Parameters方法
                //updataData();//更新数据
                //DelData();//删除数据
                //QueryDataByDs();//使用dataadapter,dataset查询数据
                //QueryByDr();//使用dataread查询数据
                //querybyDt();//使用datatable查询数据
                Console.ReadKey();
            }
    
            #region 1.1插入新数据
            private static void InsertData()
            { 
                using (SqlConnection connection = new SqlConnection(strConn))
                { 
                    connection.Open();
                    using (SqlCommand sqlcmd = connection.CreateCommand())
                    { 
                        string strCmd = "insert into Customer (region,Name,Department) values ('jinan','li','shandong')";
                        sqlcmd.CommandText = strCmd;
                        if (sqlcmd.ExecuteNonQuery() > 0)
                        { 
                            Console.WriteLine("插入成功");
                        }
                        else
                        { 
                            Console.WriteLine("插入失败");
                        }
                    }
                }
            }
            #endregion
    
            #region 1.2插入新数据,Parameters方法
            private static void InsertDataParameters()
            { 
                using (SqlConnection connection = new SqlConnection(strConn))
                { 
                    connection.Open();
                    using (SqlCommand sqlCmd = connection.CreateCommand())
                    { 
                        string sqlStr = "insert into Customer (region,Name,Department) values (@region,@name,@depart)";
                        sqlCmd.CommandText = sqlStr;
                        sqlCmd.Parameters.Add("@region", SqlDbType.NVarChar);
                        sqlCmd.Parameters["@region"].Value = "Sichuan";
                        sqlCmd.Parameters.AddWithValue("@name", "Chong");
                        sqlCmd.Parameters.AddWithValue("@depart", "qing");
                        if (sqlCmd.ExecuteNonQuery() > 0)
                        { 
                            Console.WriteLine("插入成功");
                        }
                        else
                        { 
                            Console.WriteLine("插入失败");
                        }
                    }
                }
            }
            #endregion
    
            #region 2 修改数据练习
            private static void updataData()
            { 
                using (SqlConnection connection = new SqlConnection(strConn))
                { 
                    connection.Open();
                    using (SqlCommand sqlCmd = connection.CreateCommand())
                    { 
                        string cmdStr = "update Customer set Name='@name' where id='2845'";
                        sqlCmd.CommandText = cmdStr;
                        if (sqlCmd.ExecuteNonQuery() > 0)
                        { 
                            Console.WriteLine("插入成功");
                        }
                        else
                        { 
                            Console.WriteLine("插入失败");
                        }
                    }
                }
            }
            #endregion
    
            #region 3 删除数据 练习
            private static void DelData()
            { 
                using (SqlConnection connection = new SqlConnection(strConn))
                { 
                    connection.Open();
                    using (SqlCommand sqlcmd = connection.CreateCommand())
                    { 
                        string cmdStr = "delete from Customer where id >='2840'";
                        sqlcmd.CommandText = cmdStr;
                        if (sqlcmd.ExecuteNonQuery() > 0)
                        { 
                            Console.WriteLine("删除成功");
                        }
                        else
                        { 
                            Console.WriteLine("删除失败");
                        }
                    }
                }
            }
            #endregion
    
            #region 4.1 使用dataadapter,dataset查询数据
            private static void QueryDataByDs()
            { 
                using (SqlConnection connection = new SqlConnection(strConn))
                { 
                    connection.Open();
                    string cmdStr = "select top 20 * from Customer";
                    SqlDataAdapter sqlda = new SqlDataAdapter(cmdStr, strConn);
                    DataSet ds = new DataSet();
                    sqlda.Fill(ds, "customer");
                    foreach (DataRow dr in ds.Tables[0].Rows)
                    { 
                        Console.WriteLine("ID={0},region={1},department={2},name={3},phone1={4}", dr[0], dr[1], dr[2], dr[3], dr[4]);
                    }
                }
            }
            #endregion
    
            #region 4.2 使用datareader查询数据
            private static void QueryByDr()
            { 
                using (SqlConnection connection = new SqlConnection(strConn))
                { 
                    connection.Open();
                    using (SqlCommand sqlCmd = connection.CreateCommand())
                    { 
                        string cmdStr = "select top 10 * from Customer";
                        sqlCmd.CommandText = cmdStr;
                        SqlDataReader sqlDr = sqlCmd.ExecuteReader();
                        while (sqlDr.HasRows)//是否返回数据
                        { 
                            while (sqlDr.Read())//从第一行开始顺序读取数据集到最后一行
                            { 
                                Console.WriteLine("ID={0},region={1},department={2},name={3},phone1={4}", sqlDr[0].ToString(), sqlDr[1].ToString(), sqlDr[2].ToString(), sqlDr[3].ToString(), sqlDr[4].ToString());
                            }
                        }
                    }
                }
            }
            #endregion
    
            #region 4.3 使用datatable查询数据
            private static void querybyDt()
            { 
                SqlConnection connection = new SqlConnection(strConn);
                connection.Open();
                string cmdStr = "select top 10 * from Customer";
                SqlDataAdapter sqlda = new SqlDataAdapter(cmdStr,connection);
                DataTable dt = new DataTable();
                sqlda.Fill(dt);
                foreach (DataRow item in dt.Rows)
                { 
                    Console.WriteLine("{0},{1},{2}", item[0].ToString(), item[1].ToString(), item[2].ToString());
                }
                connection.Close();
            }
            #endregion
        }