public int Insert <T>(SqlCondition condition, List <T> datas) { DataTable dt = new DataTable(); EntityBase entity = EntityManager.GetEntity(condition.ConnectionName, condition.TableName); if (entity == null) { throw new Exception("Invalid connection or table."); } Hashtable mappings = new Hashtable(); foreach (var attr in entity.Attributes.Keys) { dt.Columns.Add(attr, typeof(T).GetProperty(attr).PropertyType); mappings.Add(attr, attr); } foreach (var data in datas) { DataRow dr = dt.NewRow(); foreach (var attr in entity.Attributes.Keys) { dr[attr] = data.GetType().GetProperty(attr).GetValue(data); } dt.Rows.Add(dr); } string connStr = SqlServerConnectionProvider.Instance.GetConnectionString(condition.ConnectionName); return(SqlServerBaseOrder.ExecuteBulkCopy(connStr, condition.TableName, dt, mappings)); }
public void CreateTable(EntityBase entity, string connectionName = null) { string sqlTemplate = @"CREATE TABLE {0} ({1})"; List <string> attrList = new List <string>(); foreach (var attr in entity.Attributes.Keys) { var temp = entity.Attributes[attr].AttributeName + " " + entity.Attributes[attr].AttributeType; if (!string.IsNullOrEmpty(entity.Attributes[attr].AttributeSize) && int.Parse(entity.Attributes[attr].AttributeSize) > 0) { temp += string.Format("({0})", entity.Attributes[attr].AttributeSize); } if (entity.Attributes[attr].IsPrimaryKey) { temp += " PRIMARY KEY"; } if (entity.Attributes[attr].IsNotNull && !entity.Attributes[attr].IsPrimaryKey) { temp += " NOT NULL"; } attrList.Add(temp); } string sqlText = string.Format(sqlTemplate, entity.EntityName, string.Join(",", attrList.ToArray())); SqlServerBaseOrder.ExecuteNonQuery(SqlServerConnectionProvider.Instance.GetConnectionString(connectionName), sqlText); }
public List <T> Query <T>(SqlCondition condition) { EntityBase entity = EntityManager.GetEntity(condition.ConnectionName, condition.TableName); if (entity == null) { throw new Exception("Invalid connection or table."); } List <string> attrList = new List <string>(); foreach (var attr in entity.Attributes.Keys) { if ((condition.FilterWay == FilterWay.FilterOut && !condition.FilterColumns.Contains(attr)) || (condition.FilterWay == FilterWay.Keep && condition.FilterColumns.Count == 0) || (condition.FilterWay == FilterWay.Keep && condition.FilterColumns.Contains(attr))) { attrList.Add(attr); } } string sqlTemplate = "SELECT {0} FROM {1}"; string sqlText = string.Format(sqlTemplate, string.Join(",", attrList), condition.TableName); if (condition.Expression != null) { sqlText += condition.Expression.GenerateSqlText(); } if (condition.OrderColumns != null && condition.OrderColumns.Count > 0) { sqlText = sqlText + string.Format(" ORDER BY {0} ", string.Join(",", condition.OrderColumns)) + condition.OrderBy.ToString(); } string connStr = SqlServerConnectionProvider.Instance.GetConnectionString(condition.ConnectionName); return(SqlServerBaseOrder.ExecuteReader <T>(connStr, sqlText)); }
public int Insert <T>(SqlCondition condition, T data) { EntityBase entity = EntityManager.GetEntity(condition.ConnectionName, condition.TableName); if (entity == null) { throw new Exception("Invalid connection or table."); } List <string> attrList = new List <string>(); List <string> paramList = new List <string>(); foreach (var attr in entity.Attributes.Keys) { attrList.Add(attr); paramList.Add(string.Format("@{0}", attr)); } string sqlTemplate = "INSERT INTO {0}({1})VALUES({2})"; string sqlText = string.Format(sqlTemplate, condition.TableName, string.Join(",", attrList), string.Join(",", paramList)); List <SqlParameter> paras = new List <SqlParameter> { }; foreach (var attr in entity.Attributes.Keys) { paras.Add(this.Convert <T>(entity.Attributes[attr], data)); } string connStr = SqlServerConnectionProvider.Instance.GetConnectionString(condition.ConnectionName); return(SqlServerBaseOrder.ExecuteNonQuery(connStr, sqlText, paras.ToArray())); }
public bool TableExist(string tableName, string connectionName = null) { string sqlTemplate = "SELECT COUNT(*) FROM dbo.sysobjects WHERE name = '{0}'"; string sqlText = string.Format(sqlTemplate, tableName); string connStr = SqlServerConnectionProvider.Instance.GetConnectionString(connectionName); object result = SqlServerBaseOrder.ExecuteScalar(connStr, sqlText); return((int)result > 0 ? true : false); }
public void CreateDatabase(string connectionName = null) { string databaseName = ConnectionProviderBase.GetDatabaseName(connectionName); string sqlTemplate = "CREATE DATABASE {0}"; string sqlText = string.Format(sqlTemplate, databaseName); string connStr = SqlServerConnectionProvider.Instance.GetConnectionStringWithoutIC(connectionName); SqlServerBaseOrder.ExecuteNonQuery(connStr, sqlText); }
public int Delete(SqlCondition condition) { string sqlTemplate = "DELETE FROM {0}"; string sqlText = string.Format(sqlTemplate, condition.TableName); if (condition.Expression != null) { sqlText += condition.Expression.GenerateSqlText(); } string connStr = SqlServerConnectionProvider.Instance.GetConnectionString(condition.ConnectionName); return(SqlServerBaseOrder.ExecuteNonQuery(connStr, sqlText)); }
public int InsertIntoEntityBase(EntityBase entity, string connectionName = null) { string sqlText = "INSERT INTO EntityBase(Id, EntityName)VALUES(@Id, @EntityName)"; SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@Id", SqlDbType.NVarChar, 256), new SqlParameter("@EntityName", SqlDbType.NVarChar, 256) }; paras[0].Value = entity.Id; paras[1].Value = entity.EntityName; string connStr = SqlServerConnectionProvider.Instance.GetConnectionString(connectionName); return(SqlServerBaseOrder.ExecuteNonQuery(connStr, sqlText, paras)); }
public bool DatabaseExist(string connectionName = null) { string databaseName = ConnectionProviderBase.GetDatabaseName(connectionName); string sqlTemplate = "SELECT * FROM master.dbo.sysdatabases WHERE name='{0}'"; string sqlText = string.Format(sqlTemplate, databaseName); string connStr = SqlServerConnectionProvider.Instance.GetConnectionStringWithoutIC(connectionName); object result = SqlServerBaseOrder.ExecuteScalar(connStr, sqlText); if (result != null && string.Equals(result as string, databaseName)) { return(true); } return(false); }
public void CreateDefaultTable(string connectionName = null) { string sqlText1 = @"CREATE TABLE EntityBase(Id nvarchar(256) primary key, EntityName nvarchar(256))"; string sqlText2 = @"CREATE TABLE EntityAttributeBase ( Id nvarchar(256) primary key, EntityId nvarchar(256), AttributeName nvarchar(256), AttributeType nvarchar(256), AttributeSize nvarchar(256), IsPrimaryKey bit, IsNotNull bit )"; string connStr = SqlServerConnectionProvider.Instance.GetConnectionString(connectionName); SqlServerBaseOrder.ExecuteNonQuery(connStr, sqlText1); SqlServerBaseOrder.ExecuteNonQuery(connStr, sqlText2); }
public void CreateField(string entityName, EntityAttributeBase attributeBase, string connectionName = null) { string sqlText = "ALTER TABLE {0} ADD {1} {2}"; string temp = string.Format(sqlText, entityName, attributeBase.AttributeName, attributeBase.AttributeType); if (!string.IsNullOrEmpty(attributeBase.AttributeSize) && int.Parse(attributeBase.AttributeSize) > 0) { temp += string.Format("({0})", attributeBase.AttributeSize); } if (attributeBase.IsPrimaryKey) { temp += " PRIMARY KEY"; } if (attributeBase.IsNotNull && !attributeBase.IsPrimaryKey) { temp += " NOT NULL"; } SqlServerBaseOrder.ExecuteNonQuery(SqlServerConnectionProvider.Instance.GetConnectionString(connectionName), temp); }
public int Update <T>(SqlCondition condition, T data) { EntityBase entity = EntityManager.GetEntity(condition.ConnectionName, condition.TableName); if (entity == null) { throw new Exception("Invalid connection or table."); } List <string> attrList = new List <string>(); List <string> expressionList = new List <string>(); foreach (var attr in entity.Attributes.Keys) { if ((condition.FilterWay == FilterWay.FilterOut && !condition.FilterColumns.Contains(attr)) || (condition.FilterWay == FilterWay.Keep && condition.FilterColumns.Count == 0) || (condition.FilterWay == FilterWay.Keep && condition.FilterColumns.Contains(attr))) { attrList.Add(attr); expressionList.Add(string.Format("{0} = @{0}", attr)); } } string sqlTemplate = "UPDATE {0} SET {1}"; string sqlText = string.Format(sqlTemplate, condition.TableName, string.Join(",", expressionList)); if (condition.Expression != null) { sqlText += condition.Expression.GenerateSqlText(); } List <SqlParameter> paras = new List <SqlParameter> { }; foreach (var attr in attrList) { paras.Add(this.Convert <T>(entity.Attributes[attr], data)); } string connStr = SqlServerConnectionProvider.Instance.GetConnectionString(condition.ConnectionName); return(SqlServerBaseOrder.ExecuteNonQuery(connStr, sqlText, paras.ToArray())); }
public Dictionary <string, EntityBase> QueryEntityDic(string connectionName = null) { Dictionary <string, EntityBase> dic = new Dictionary <string, EntityBase>(); List <EntityBase> entities = SqlServerBaseOrder.ExecuteReader <EntityBase>(SqlServerConnectionProvider.Instance.GetConnectionString(connectionName), "SELECT * FROM EntityBase"); List <EntityAttributeBase> attributes = SqlServerBaseOrder.ExecuteReader <EntityAttributeBase>(SqlServerConnectionProvider.Instance.GetConnectionString(connectionName), "SELECT * FROM EntityAttributeBase"); foreach (EntityBase entity in entities) { if (!dic.ContainsKey(entity.EntityName)) { foreach (EntityAttributeBase attr in attributes) { if (string.Equals(entity.Id, attr.EntityId)) { entity.Attributes.Add(attr.AttributeName, attr); } } dic.Add(entity.EntityName, entity); } } return(dic); }
public int InsertIntoEntityAttributeBase(EntityAttributeBase attributeBase, string connectionName = null) { string sqlText = "INSERT INTO EntityAttributeBase(Id, EntityId, AttributeName, AttributeType, AttributeSize, IsPrimaryKey, IsNotNull)VALUES(@Id, @EntityId, @AttributeName, @AttributeType, @AttributeSize, @IsPrimaryKey, @IsNotNull)"; SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@Id", SqlDbType.NVarChar, 256), new SqlParameter("@EntityId", SqlDbType.NVarChar, 256), new SqlParameter("@AttributeName", SqlDbType.NVarChar, 256), new SqlParameter("@AttributeType", SqlDbType.NVarChar, 256), new SqlParameter("@AttributeSize", SqlDbType.NVarChar, 256), new SqlParameter("@IsPrimaryKey", SqlDbType.Bit), new SqlParameter("@IsNotNull", SqlDbType.Bit) }; paras[0].Value = attributeBase.Id; paras[1].Value = attributeBase.EntityId; paras[2].Value = attributeBase.AttributeName; paras[3].Value = attributeBase.AttributeType; paras[4].Value = string.IsNullOrEmpty(attributeBase.AttributeSize) ? (object)DBNull.Value : attributeBase.AttributeSize; paras[5].Value = attributeBase.IsPrimaryKey; paras[6].Value = attributeBase.IsNotNull; string connStr = SqlServerConnectionProvider.Instance.GetConnectionString(connectionName); return(SqlServerBaseOrder.ExecuteNonQuery(connStr, sqlText, paras)); }