ADO.NET データベース接続方式

来源：互联网发布：淘宝u站报名编辑：程序博客网时间：2024/05/17 10:09

・SqlDataReaderを使用、データの読み出しをする

   public static void SqlconnectByDR()
        {
            string path = "server=XXX;database =Test;Integrated Security =true";
            SqlConnection cn = new SqlConnection(path);
            //SqlConnection cn = new SqlConnection();
            //sqlconn.ConnectionString = path;
            string sql = "select * from T_Student";
            try
            {
                cn.Open();
                SqlCommand cmd = new SqlCommand(sql, cn);
                SqlDataReader dr = cmd.ExecuteReader(); //SqlDataReader
                while (dr.Read())
                {
                    Console.WriteLine("{0}\t{1}", dr[0], dr[1]);
                }
                dr.Close();
                Console.ReadKey();
            }
            catch (SqlException ex)
            {
                Console.WriteLine(ex.Message);
            }
            finally
            {
                cn.Close();
            }
        }

・SqlDataAdapterを使用、データの読み出しをする

        public static void SqlconnectByDA()
        {
            string con = "server=NXXX-A-0122;database=Test;Integrated Security=true";
            SqlConnection sqlcon = new SqlConnection(con);
            string sql = "select * from T_Student";
            try
            {
                SqlDataAdapter dataAdapter = new SqlDataAdapter(sql, sqlcon);
                DataSet dataSet = new DataSet();

                dataAdapter.Fill(dataSet);
                int rowNum = dataSet.Tables[0].Rows.Count;
                int colNUm = dataSet.Tables[0].Columns.Count;
                for (int i = 0; i != rowNum; ++i)
                {
                    for (int j = 0; j < colNUm; j++)
                    {
                        Console.Write("{0}\t", dataSet.Tables[0].Rows[i].ItemArray[j]);
                        if (j == (colNUm - 1))
                        {
                            Console.WriteLine();
                        }
                    }
                }
                Console.ReadKey();
            }
            catch (SqlException ex)
            {
                Console.WriteLine(ex.Message);
            }
            finally
            {
                sqlcon.Close();
            }
        }

・OleDbDataReaderを使用、データの読み出しをする

        public static void SqlconnectByOleDb()
        {
            string conn = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=Test;Data Source=NB583S-A-0122";
            OleDbConnection oledbconn = new OleDbConnection(conn);
            string sql = "select * from T_Student";
            try
            {
                oledbconn.Open();
                //oledbconn.State.ToString();
                OleDbCommand oledbcom = new OleDbCommand(sql, oledbconn);
                //int num = (int)oledbcom.ExecuteScalar();
                OleDbDataReader dr = oledbcom.ExecuteReader();
                while (dr.Read())
                {
                    Console.WriteLine("{0}\t{1}", dr[0], dr[1]);
                }
                dr.Close();
                //Console.WriteLine(num.ToString());
                Console.ReadKey();
            }
            catch (OleDbException ex)
            {
                Console.WriteLine(ex.Message);
            }
            finally
            {
                oledbconn.Close();
            }
        }

・ストアドプロシージャを使用、データ保存処理をする

        public static void SqlUseProc()
        {
            string conn = "server=NB5XXX0122;database =Test;Integrated Security =true";
            SqlConnection sqlconn = new SqlConnection(conn);
            try
            {
                sqlconn.Open();
                SqlCommand sqlcmd= new SqlCommand();
                sqlcmd.CommandType = CommandType.StoredProcedure;
                sqlcmd.CommandText = "userAdd";
                sqlcmd.Connection = sqlconn;

                //@id varchar(4),
                //@username varchar(15),
                //@userpwd varchar(15),
                //@useraddress varchar(15)
                SqlParameter id = new SqlParameter("id", "0009");
                SqlParameter username = new SqlParameter("username", "石田沙希");
                SqlParameter userpwd = new SqlParameter("userpwd", "issk1990");
                SqlParameter useraddress = new SqlParameter("useraddress", "埼玉県朝霞");

                sqlcmd.Parameters.Add(id);
                sqlcmd.Parameters.Add(username);
                sqlcmd.Parameters.Add(userpwd);
                sqlcmd.Parameters.Add(useraddress);

                sqlcmd.ExecuteNonQuery();
                Console.WriteLine("Is Ok");
                Console.ReadKey();
            }
            catch (SqlException e)
            {
                Console.WriteLine(e.Message);
            }
            finally
            {
                sqlconn.Close();
            }
        }

阅读全文

0 0