C# Sql.SqlDatabase类代码示例

        public int addNewUser(string firstname, string lastname, string companyname, string email, string password, string countryid, string stateid, string mobile, int type, string verify)
        {
            int status = 0;
            try
            {
                Guid guid = Guid.NewGuid();
                Database objDB = new SqlDatabase(connectionStr);
                DbCommand objAdd = new SqlCommand();
                objAdd.CommandType = CommandType.StoredProcedure;
                objAdd.CommandText = "InsertUser";
                objDB.AddInParameter(objAdd, "@FName", DbType.String, firstname);
                objDB.AddInParameter(objAdd, "@LName", DbType.String, lastname);
                objDB.AddInParameter(objAdd, "@Companyname", DbType.String, companyname);
                objDB.AddInParameter(objAdd, "@Email", DbType.String, email);
                objDB.AddInParameter(objAdd, "@Password", DbType.String, password);
                objDB.AddInParameter(objAdd, "@Countryid", DbType.Int32, countryid);
                objDB.AddInParameter(objAdd, "@Stateid", DbType.Int32, stateid);
                objDB.AddInParameter(objAdd, "@Mobile", DbType.String, mobile);
                objDB.AddInParameter(objAdd, "@Type", DbType.Int32, type);
                objDB.AddInParameter(objAdd, "@Verify", DbType.String, verify);
                objDB.AddOutParameter(objAdd, "@Stat", DbType.Int16, 16);
                objDB.ExecuteNonQuery(objAdd);
                status = Convert.ToInt16(objDB.GetParameterValue(objAdd, "@Stat"));

                return status;
            }
            catch (Exception ex)
            {
                objErr.GeneralExceptionHandling(ex, "Website - User Registration", "addNewUser", "GENERAL EXCEPTION");
                return status;
            }
        }

        protected void btnProcessManualBox_Click( object sender, EventArgs e )
        {
            StatGrabber.StatGrabber sg = new StatGrabber.StatGrabber();
            SqlDatabase db = new SqlDatabase( System.Configuration.ConfigurationManager.AppSettings["ConnectionString"] );
            ArrayList problems = new ArrayList();
            try
            {
                ArrayList perfs = sg.GetGamePerformances( tbManualBoxURL.Text, problems );
                tbOutput.Text += "Got " + perfs.Count + " perfs from " + tbManualBoxURL.Text + "\r\n";
                problems.AddRange( sg.SavePerformances( db, perfs, calStatDate.SelectedDate ) );
            }
            catch( StatGrabber.StatGrabberException ex )
            {
                tbOutput.Text += ex.Message;
            }
            if( problems.Count > 0 )
            {
                tbOutput.Text += "Problems:\r\n";
                foreach( StatGrabber.PlayerPerformance p in problems )
                {
                    tbOutput.Text += p.FirstName + " " + p.LastName + " " + p.TeamName + "\r\n";
                }
            }
            else
            {
                tbOutput.Text += "No problems identifying players\r\n";
            }

            tbOutput.Text += sg.UpdateAveragesAndScores( db, calStatDate.SelectedDate );

            Log.AddLogEntry(
                LogEntryTypes.StatsProcessed,
                Page.User.Identity.Name,
                tbOutput.Text );
        }

        public int addInquiry(string name, string email, string subject, string message)
        {
            int status = 0;
            try
            {
                Database objDB = new SqlDatabase(connectionStr);
                DbCommand objAdd = new SqlCommand();
                objAdd.CommandType = CommandType.StoredProcedure;
                objAdd.CommandText = "InsertInquiry";
                objDB.AddInParameter(objAdd, "@Name", DbType.String, name);
                objDB.AddInParameter(objAdd, "@Email", DbType.String, email);
                objDB.AddInParameter(objAdd, "@Subject", DbType.String, subject);
                objDB.AddInParameter(objAdd, "@Message", DbType.String, message);
                objDB.AddOutParameter(objAdd, "@Stat", DbType.Int16, 16);
                objDB.ExecuteNonQuery(objAdd);
                status = Convert.ToInt16(objDB.GetParameterValue(objAdd, "@Stat"));

                return status;
            }
            catch (Exception ex)
            {
                objCommom.LogFile("Contact.aspx", "addInquiry", ex);
                return status;
            }
        }

        public override CustomerList GetCustomer(string vipCode)
        {
            SqlDatabase database = new SqlDatabase(ConnectionString);
            DbCommand command = database.GetStoredProcCommand("rmSP_WSPOS_GetCustomer");
            command.CommandTimeout = 300;
            database.AddInParameter(command, "@VipCode", DbType.String, vipCode);

            List<Customer> customers = new List<Customer>();
            using (IDataReader reader = database.ExecuteReader(command))
            {
                while (reader.Read())
                {
                    Customer customer = new Customer();
                    customer.CustomerId = Convert.ToInt32(reader["VIPCode_id"]);
                    customer.CustomerCode = reader["VipCode"] as string;
                    customer.FirstName = reader["VIPGName"] as string;
                    customer.LastName = reader["VIPName"] as string;
                    customer.Telephone = reader["VIPTel"] as string;
                    if (reader["VIPBDay"] != DBNull.Value)
                        customer.BirthDate = Convert.ToDateTime(reader["VIPBDay"]);

                    customers.Add(customer);
                }
            }

            CustomerList customerList = new CustomerList();
            customerList.Customers = customers;
            customerList.TotalCount = customers.Count;
            return customerList;
        }

 protected string Autosub( string weekId, SqlDatabase db, DateTime selectedDate )
 {
     string result = AutoSub.ProcessAutosubs( weekId );
     StatGrabber.StatGrabber sg = new StatGrabber.StatGrabber();
     result += sg.UpdateAveragesAndScores( db, selectedDate );
     return result;
 }

        protected void Page_Load(object sender, EventArgs e)
        {
            if (!IsPostBack)
            {
                Database objDB = new SqlDatabase(M_CONSTR);
                if (objDB != null)
                {
                    //所属部门
                    DataSet ds = objDB.ExecuteDataSet(CommandType.Text, "SELECT * FROM Table_Group");
                    ddlTGroup.DataSource = ds.Tables[0];
                    ddlTGroup.DataTextField = "GroupName";
                    ddlTGroup.DataValueField = "GroupID";
                    ddlTGroup.DataBind();
                    ddlTGroup.Items.Insert(0, (new ListItem("--全部--", "0")));
                    ddlTGroup.SelectedIndex = 0;

                    //讲师
                    ds = objDB.ExecuteDataSet(CommandType.Text, "SELECT * FROM Table_TEACHER");
                    ddlTeacher.DataSource = ds.Tables[0];
                    ddlTeacher.DataTextField = "TeacherName";
                    ddlTeacher.DataValueField = "TeacherID";
                    ddlTeacher.DataBind();
                    //ddlTeacher.Items.Insert(0, (new ListItem("--全部--", "0")));
                    //ddlTeacher.SelectedIndex = 0;
                }
            }
        }

		public void DoLotsOfConnectionFailures()
		{
			int numberOfEvents = 50;
			using (WmiEventWatcher eventListener = new WmiEventWatcher(numberOfEvents))
			{
				SqlDatabase db = new SqlDatabase("BadConnectionString");
				DataInstrumentationListener listener = new DataInstrumentationListener("foo", true, true, true);
				DataInstrumentationListenerBinder binder = new DataInstrumentationListenerBinder();
				binder.Bind(db.GetInstrumentationEventProvider(), listener);

				for (int i = 0; i < numberOfEvents; i++)
				{
					try
					{
						db.ExecuteScalar(CommandType.Text, "Select count(*) from Region");
					}
					catch { }
				}

				eventListener.WaitForEvents();
				
				Assert.AreEqual(numberOfEvents, eventListener.EventsReceived.Count);
                Assert.AreEqual("ConnectionFailedEvent", eventListener.EventsReceived[0].ClassPath.ClassName);
				Assert.AreEqual("foo", eventListener.EventsReceived[0].GetPropertyValue("InstanceName"));
				Assert.AreEqual(db.ConnectionStringWithoutCredentials, eventListener.EventsReceived[0].GetPropertyValue("ConnectionString"));
			}
		}

 public static IDataReader Departments()
 {
     SqlDatabase db = new SqlDatabase(connString);
     DbCommand command = db.GetSqlStringCommand("SELECT DeptId, Name FROM AllDepartments D WHERE D.Active=1 AND D.NormBillsTime=1 ORDER BY Name");
     command.CommandType = CommandType.Text;
     return db.ExecuteReader(command);
 }

        public IDataReader ToDataReader(string storedProcedureName, Dictionary<string, string> parameters)
        {
            using (SqlCommand cmd = new SqlCommand(storedProcedureName))
            {
                cmd.CommandType = CommandType.StoredProcedure;
                foreach (string key in parameters.Keys)
                {
                    SqlParameter parameter = new SqlParameter(key, parameters[key]);

                    if (parameter.Value == null)
                        parameter.Value = DBNull.Value;

                    if (parameter.SqlDbType == SqlDbType.DateTime && parameter.Value is DateTime && (DateTime)parameter.Value == default(DateTime))
                        parameter.Value = SqlDateTime.MinValue.Value;

                    cmd.Parameters.Add(parameter);

                }

                SqlDatabase database = new SqlDatabase(ConfigurationManager.ConnectionStrings["MyAppsLocal"].ConnectionString);

                try
                {
                    IDataReader reader = database.ExecuteReader(cmd);
                    return reader;
                }
                catch (Exception e)
                {
                    Console.WriteLine(e.ToString());
                    return null;
                }

            }
        }

        public static DataTable GetResources(int jobId, int deptId)
        {
            SqlDatabase db = new SqlDatabase(connString);
            string sql = @" SELECT	U.UserId, U.FirstName + ' ' + U.LastName AS FullName, 
		                            t.TitleName as Title
                            FROM	AllocableUsers U LEFT JOIN JobTitles AS t ON U.currentTitleID=t.TitleID
                            WHERE	U.Active=1 AND U.UserId NOT IN (
                                    SELECT UserId FROM Assignments WHERE [email protected]_id
                                    AND (EndDate IS NULL OR EndDate>DATEADD(s, 1, CURRENT_TIMESTAMP))) 
                                    AND [email protected]_id AND realPerson='Y'
		                            AND UserId NOT IN (
			                            SELECT	UserId
			                            FROM	timeEntry
			                            WHERE	[email protected]_id AND UserId=U.UserId AND (TimeSpan IS NULL OR TimeSpan > 0)
		                            )
                            ORDER BY FullName";

            DbCommand command = db.GetSqlStringCommand(sql);
            db.AddInParameter(command, "@job_id", DbType.Int32, jobId);
            db.AddInParameter(command, "@dept_id", DbType.Int32, deptId);
            DataTable t = new DataTable();
            t = db.ExecuteDataSet(command).Tables[0].Copy();
            t.TableName = "Resources";
            command.Dispose();
            return t;
        }

 public static IDataReader GetIDataReader(string connectionString, string sqlQuery)
 {
     SqlDatabase sqlServerDB = new SqlDatabase(connectionString);
     DbCommand cmd = sqlServerDB.GetSqlStringCommand(sqlQuery);
     //return an IDataReader.
     return sqlServerDB.ExecuteReader(cmd);
 }

        //fetch record
        public static DataTable GetImportedRecords(int? top = 1000, bool direction = true, string name = "",
             bool? gender = null, int? year = null, long? rank = null)
        {
            Database objDB = new SqlDatabase(ConfigurationManager.ConnectionStrings["DBaseConnectionString"].ConnectionString);
            DataSet _ds = new DataSet();
            using (DbCommand objCMD = objDB.GetStoredProcCommand("PSP_Babies_Get"))
            {
                objDB.AddInParameter(objCMD, "@Top",
                                     DbType.Int32, top??1000000);
                objDB.AddInParameter(objCMD, "@SortingDirection",
                                     DbType.String, direction.ToIndicator());

                objDB.AddInParameter(objCMD, "@Name",
                                     DbType.String, name);
                objDB.AddInParameter(objCMD, "@Gender",
                                     DbType.String, gender.ToIndicator());
                objDB.AddInParameter(objCMD, "@Year",
                                     DbType.Int32, year);
                objDB.AddInParameter(objCMD, "@Rank",
                                     DbType.Int64, rank);

                try
                {
                    _ds = objDB.ExecuteDataSet(objCMD);
                    return _ds != null ? _ds.Tables[0] : new DataTable();
                }
                catch (Exception ex)
                {
                    throw ex;
                }
            }
        }

        public static void SaveBaby(string babyName, long position, bool gender, int year, long rank)
        {
            Database objDB = new SqlDatabase(ConfigurationManager.ConnectionStrings["DBaseConnectionString"].ConnectionString);
            using (DbCommand objCMD = objDB.GetStoredProcCommand("PSP_Babies_Save"))
            {
                objDB.AddInParameter(objCMD, "@Name",
                                     DbType.String, babyName);
                objDB.AddInParameter(objCMD, "@Gender",
                                     DbType.String, gender.ToIndicator());
                objDB.AddInParameter(objCMD, "@Position",
                                    DbType.Int64, position);
                objDB.AddInParameter(objCMD, "@Rank",
                                     DbType.Int64, rank);
                objDB.AddInParameter(objCMD, "@Year",
                                    DbType.Int32, year);

                //objDB.AddOutParameter(objCMD, "@strMessage", DbType.String, 255);

                try
                {
                    objDB.ExecuteNonQuery(objCMD);
                }
                catch (Exception ex)
                {
                    throw ex;
                }
            }
        }

        public int addNewProductPost(string userid, int l1id, int l2id, int l3id, string productname, string keywords, string description, decimal quantity, decimal price, string image1,
            string image2, string image3, string image4)
        {
            int status = 0;
            try
            {

                Guid guid = new Guid(userid);
                Database objDB = new SqlDatabase(connectionStr);
                DbCommand objAdd = new SqlCommand();
                objAdd.CommandType = CommandType.StoredProcedure;
                objAdd.CommandText = "InsertProductPost";
                objDB.AddInParameter(objAdd, "@USERID", DbType.Guid, guid);
                objDB.AddInParameter(objAdd, "@L1ID", DbType.Int32, l1id);
                objDB.AddInParameter(objAdd, "@L2ID", DbType.Int32, l2id);
                objDB.AddInParameter(objAdd, "@L3ID", DbType.Int32, l3id);
                objDB.AddInParameter(objAdd, "@PRODUCTNAME", DbType.String, productname);
                objDB.AddInParameter(objAdd, "@DESCRIPTION", DbType.String, description);
                objDB.AddInParameter(objAdd, "@QUANTITY", DbType.Decimal, quantity);
                objDB.AddInParameter(objAdd, "@PRICE", DbType.Decimal, price);
                objDB.AddInParameter(objAdd, "@KEYWORDS", DbType.String, keywords);
                objDB.AddInParameter(objAdd, "@IMAGE1", DbType.String, image1);
                objDB.AddInParameter(objAdd, "@IMAGE2", DbType.String, image2);
                objDB.AddInParameter(objAdd, "@IMAGE3", DbType.String, image3);
                objDB.AddInParameter(objAdd, "@IMAGE4", DbType.String, image4);
                objDB.AddOutParameter(objAdd, "@Stat", DbType.Int16, 16);
                objDB.ExecuteNonQuery(objAdd);
                status = Convert.ToInt16(objDB.GetParameterValue(objAdd, "@Stat"));
            }
            catch (Exception ex)
            {
                objErr.GeneralExceptionHandling(ex, "Website - Post Product", "addNewProductPost", "GENERAL EXCEPTION");
            }
            return status;
        }

        public void NoEventBroadcastIfNoEventRegistered()
        {
            string connectionString = @"server=(local)\sqlexpress;database=northwind;integrated security=true;";
            SqlDatabase db = new SqlDatabase(connectionString);

            db.ExecuteNonQuery(CommandType.Text, "Select count(*) from Region");
        }